Hi Everybody!
In a table I have a field containing date in the form of dd/mm/yyyy. I would like to build a sql code which would look into all these dates and:
- Detect the month of the date (so if mm = 01 then 'January', if mm = 02 then 'February', ....)
- within each month, look for the date which is the closest to month end
- When I have the closest dates to month end (usually 3 as I work by quarters), run my select query using these dates: Where pos_date in ('closest date to month end January','closest date to month
end February',...)
I have no clue how to do this in sql, for the moment I do it in VBA but it forces me to have a dummy spreadsheet with the database connexion to first retrieve all dates, then loop on all dates to find the closest date to month end and then I can build my query using the 3 dates (closest to month end) in another spreadsheet, which is messy.
To give you an idea here is how I found the closest date to month end in vba:
dat1month = January
dat2month = February
dat3month = March
For i = 2 To LR
If MonthName(Month(ActiveSheet.Cells(i, 3))) = dat1month Then
If ActiveSheet.Cells(i, 3) > Dat1max Then
Dat1max = ActiveSheet.Cells(i, 3)
End If
ElseIf MonthName(Month(ActiveSheet.Cells(i, 3))) = dat2month Then
If ActiveSheet.Cells(i, 3) > Dat2max Then
Dat2max = ActiveSheet.Cells(i, 3)
End If
ElseIf MonthName(Month(ActiveSheet.Cells(i, 3))) = dat3month Then
If ActiveSheet.Cells(i, 3) > Dat3max Then
Dat3max = ActiveSheet.Cells(i, 3)
End If
End If
Next i
These 3 dates are then used in another query to pull data only for these 3 dates.
Many Thanks!