Group a dates by range

Question

I'm struggling with a T-SQL query to partitions/group a dates table into periods. A new period is defined as from 16th of a month to 15th the next month.

So if the table looks like this:

2022-05-13
2022-05-14
2022-05-15
2022-05-16
2022-05-17
2022-05-18
2022-05-19
2022-05-20
2022-05-21
2022-05-22
2022-05-23
2022-05-24
2022-05-25
2022-05-26
..
..

The expected result would be:

2022-05-13   1
2022-05-14   1
2022-05-15   1
2022-05-16   2
2022-05-17   2
2022-05-18   2
2022-05-19   2
2022-05-20   2
2022-05-21   2
2022-05-22   2
2022-05-23   2
2022-05-24   2
2022-05-25   2
2022-05-26   2
..
..
2022-06-15   2
2022-06-16   3

I suspect I need a window function here of sorts, but I am very inexperienced with those. Programatically I would loop each date, and lookahead to the period date (16th of next month), but I need this in SQL.

What I have tried is this:

WITH T AS
(
SELECT Date, ROW_NUMBER() OVER (ORDER BY Date) AS Period
FROM dbo.Dates
WHERE DayOfMonth = 16

)
SELECT D.Date, Period  FROM dbo.Dates D
LEFT JOIN T ON D.Date = T.Date

However it is incomplete and will not fill out the null values after the join.

The answer you want is database dependent. Which SQL _database_ are you using (e.g. MySQL, SQL Server, Oracle, etc.) ? — Tim Biegeleisen, May 16 '22 at 10:54
https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, May 16 '22 at 10:55
Use row_number for all the dates with day = 16 and then lag for everything else. — SMor, May 16 '22 at 11:06

score 0 · Answer 1 · answered May 16 '22 at 15:18

It's pretty simple using window functions.

You need to subtract 15 days, then get the end of that month.

Then you use DENSE_RANK to get the ranking

SELECT *,
  DENSE_RANK() OVER (ORDER BY EOMONTH(DATEADD(day, -15, d.date)))
FROM Dates d;

db<>fiddle

Date	Rank
2022-05-13	1
2022-05-14	1
2022-05-15	1
2022-05-16	2
2022-05-17	2
2022-05-18	2
2022-05-19	2
2022-05-20	2
2022-05-21	2
2022-05-22	2
2022-05-23	2
2022-05-24	2
2022-05-25	2
2022-05-26	2

Group a dates by range

1 Answers1