CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
CodeNinja
CodeNinja

Reputation: 3278

Adding Year and Month to a group by sql query

I have the following query where @BeginTime is the first day of the month and @EndTime is the last day of the month .

SET @BeginTime = RTRIM(@BeginTime) + ' 00:00:00'
SET @EndTime   = RTRIM(@EndTime) + ' 23:59:59'

select C.Name , COUNT(DISTINCT D.Id)  from DriverLic D 
INNER JOIN Clov C WITH (NOLOCK) ON D.CId = C.CId 

AND ((D.RDate < @EndTime) 
AND ( D.UrDate > @BeginTime))

group by C.Name

I get an output something like this :

Name  Count(D.Id)
AC        22
AB        32
CD        11

I would like to get an output something like this : 

Year Month Name Count(D.id)
2013  8     AC      22
2013  8     AB      32
2013  8     CD      11

Is there a way i can achieve this ?

Upvotes: 1

Views: 2130

Answers (1)

Nathan Koop
Nathan Koop

Reputation: 25197

Yep,

SELECT Year(yourDateColumn) AS 'Year', Month(yourDateColumn) AS 'Month', C.Name, COUNT(DISTINCT D.Id)  
from DriverLic D 
    INNER JOIN Clov C WITH (NOLOCK) ON D.CId = C.CId 
--WHERE your where conditions should go here...
GROUP BY 
    Year(yourDateColumn), Month(yourDateColumn), C.Name

Upvotes: 4

Related Questions