# Count and group rows for six months prior

I've written the following query for SQL server:

SELECT YEAR(Meeting_Date) year, MONTH(Meeting_Date) month, COUNT(project) meetings
FROM
meetings
WHERE Meeting_Date BETWEEN
AND
DATEADD(d,-1,CAST(DATEPART(year,GETDATE()) as varchar(4)) + '-' + CAST(DATEPART(month, GETDATE()) as varchar(2)) + '-01')
GROUP BY YEAR(Meeting_Date), MONTH(Meeting_Date)
ORDER BY YEAR(Meeting_Date), MONTH(Meeting_Date)


This code counts up how many meetings there were for the six months prior to this one. It works perfectly, but I'm sure there's a better way of figuring the months than I have.

-

This is one of those things.... date manipulation in SQL.

For performance reasons, it is a pain to cast to NVarchar, etc. For readability reasons, everything is a pain.....

What I have found works best for me, is the following function:

DateAdd(d, 1 - DatePart(d, actualdate), actualdate)


The above subtracts the number of days needed to bring a date back to the first-of-the-month.

For example today, (the 28th April), would subtract 1 - 28 (or 27) days from the 28th, to get the 1st.

You can do your current month-manipulation to move months around once you are back on to the first-of-the-month, and then you don't have to worry about the month overflow problems..

For readability reasons, I would recommend you use variables, if you can:

declare @thismonth as Date = Convert(DateAdd(d, 1 - DatePart(d, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) as Date);
declare @firstmonth as Date = DateAdd(m, -6, @thismonth);

SELECT YEAR(Meeting_Date) year,
MONTH(Meeting_Date) month,
COUNT(project) meetings
FROM
meetings
WHERE Meeting_Date BETWEEN @firstmonth AND @thismonth
GROUP BY YEAR(Meeting_Date), MONTH(Meeting_Date)
ORDER BY YEAR(Meeting_Date), MONTH(Meeting_Date)

-
here I was writing an answer and then I read your code and it does exactly what I was about to write. +1 – Malachi Apr 28 '14 at 15:46