SQL Server - SQL syntax (Newbie Question)

Asked By Alastair MacFarlane

19-Aug-08 10:55 AM

Dear Group,

I have a table (Members) which has one field (for arguement's sake) which is
a datetime field called JoinDate which stores various dates.

I would like to create a SQL statement that could give me the aggregated
result:

August : 3
July : 45
June: 84

It is a total for each month. I would assume that I would use the DatePart
function to group and then count and specify that I only want the last three
months data but I am unsure of how to do this exactly.

Any hints would be appreciated. Thanks again.

Alastair MacFarlane

21-Aug-08 11:08 PM

Select DatePart(mm, JoinDate), Count(DatePart(mm, JoinDate)) From
Members Group By DatePart(mm, JoinDate)

Hope I understood the problem correct. This query may help...


-ArunDhaJ

21-Aug-08 11:08 PM

You can use the DATENAME function if you need to get results as
posted:

SELECT DATENAME(MONTH, JoinDate) AS join_month,
COUNT(*) AS cnt
FROM Members
GROUP BY DATENAME(MONTH, JoinDate);

However, this may not give you the correct results if the data spans
over multiple years. You can include the year in the grouping:

SELECT CONVERT(CHAR(7), JoinDate, 126) AS join_year_month,
COUNT(*) AS cnt
FROM Members
GROUP BY CONVERT(CHAR(7), JoinDate, 126);


Plamen Ratchev
http://www.SQLStudio.com

19-Aug-08 11:29 AM

ArunDhaJ

Thanks for the speedy reply.

How could I specify for the last three months including the month we are
currently in? I presume this would be the WHERE clause.

Thanks again.

Alastair

21-Aug-08 11:08 PM

I forgot to include the conditions for the last 3 months:

SELECT DATENAME(MONTH, JoinDate) AS join_month,
COUNT(*) AS cnt
FROM Members
WHERE JoinDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
- 2,
0)
AND JoinDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
+ 1,
0)
GROUP BY DATENAME(MONTH, JoinDate);

SELECT CONVERT(CHAR(7), JoinDate, 126) AS join_year_month,
COUNT(*) AS cnt
FROM Members
WHERE JoinDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
- 2,
0)
AND JoinDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP)
+ 1,
0)
GROUP BY CONVERT(CHAR(7), JoinDate, 126);


Plamen Ratchev
http://www.SQLStudio.com