Find closest date to month end

Transact-SQL
1

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:

  1. Detect the month of the date (so if mm = 01 then 'January', if mm = 02 then 'February', ....)
  2. within each month, look for the date which is the closest to month end
  3. 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!

2

Store the data in a column with DATATYPE of DATE (or DATETIME if you also have a time element).

Then, in SQL, you have access to a range of date functions that will make the things you refer to trivial.

(GetDate() is a function in SQL that returns current date & time, You could use your Column Name instead, but these examples will work stand-alone)

  1. Detect the month of the date
SELECT	DATEPART(Month, GetDate())	-- Result = 5
  1. within each month, look for the date which is the closest to month end
-- Sample data for May
SELECT CONVERT(Date, '20160401') AS MyDate
INTO #TEMP
UNION ALL SELECT '20160420'	-- Highest value
UNION ALL SELECT '20160412'

SELECT	MAX(MyDate)
FROM	#TEMP
WHERE	DATEPART(Month, MyDate) = 4
-- Probably also need this:
	AND DATEPART(Year, MyDate) = 2016
-- or this:
	AND (MyDate >= '20160101' AND MyDate < '20170101')

-- For multiple months:
-- Add sample data for Jan, Feb and Mar:
INSERT INTO #TEMP -- Add more data, for other months
SELECT '20160130'	-- Highest value
UNION ALL SELECT '20160115'
UNION ALL SELECT '20160228'	-- Highest value
UNION ALL SELECT '20160207'
UNION ALL SELECT '20160324'	-- Highest value
UNION ALL SELECT '20160302'

SELECT	DATEPART(Month, MyDate) AS [Month],
	MAX(MyDate) AS [MaxDate]
FROM	#TEMP
WHERE	(MyDate >= '20160101' AND MyDate < '20170101')
GROUP BY DATEPART(Month, MyDate)
ORDER BY DATEPART(Month, MyDate)
  1. Where pos_date in ('closest date to month end January','closest date to month
    end February
-- Get last-date-found for each month in the quarter
SELECT	MAX(CASE WHEN DATEPART(Month, MyDate) = 1 THEN MyDate ELSE '19000101' END) AS [January],
	MAX(CASE WHEN DATEPART(Month, MyDate) = 2 THEN MyDate ELSE '19000101' END) AS [February],
	MAX(CASE WHEN DATEPART(Month, MyDate) = 3 THEN MyDate ELSE '19000101' END) AS [March]
FROM	#TEMP
WHERE	(MyDate >= '20160101' AND MyDate < '20160401')

-- Results:
January    February   March      
---------- ---------- ---------- 
2016-01-30 2016-02-28 2016-03-24

This last one would need some sort of parameters so that the set of three dates moves as the quarter you need changes through the year.

1 Like
3

I would have been nice, if you posted the query where you actually need these 3 dates. Makes it a lot easier to suggest a solution.

@Kristen has given you at very nice tutorial and to add to his answer, I'd just want to show you, how to get last quarter dates:

WHERE MyDate>=dateadd(quarter,datediff(quarter,0,current_timestamp)-1,0)
  AND MyDate<dateadd(quarter,datediff(quarter,0,current_timestamp),0)
4

Forum's formatting has wrecked, yet again, my referenced paragraph numbering. I hate Markdown, as compared to Markup ...

5

You are not the only one.
Also I miss being able to colorize portions of a query (like in the old forum).

6

I read somewhere that not allowing highlighting in code blocks is deliberate - in the sense that the authors considered as never being necessary. They've obviously never tried to show a poster a single missing comma in their code ...