Need help aggregating custom months into a snapshot field

user_13084623 0
Viewed 33

I am working with a dataset that has a continues date field and I want to aggregate it at a monthly level where the month ends on the 15th day of the month. So each snapshot date would go from the 15th of the month to the 14th of the following month.

Example: Snapshot Date = 7/15/2021 would correspond with the date range of 6/15/2021 through 7/14/2021.

Is there an easy way to do this for all months in the table using SQL Server.

tsql sql-server sql
1 Answers

Just subtract 14 days and convert to a year/month format. One trick is to move everything to the last day of the month:

select eomonth(dateadd(day, -14, datecol)), count(*)
from t
group by eomonth(dateadd(day, -14, datecol));
user_11440350
Related