CODEWITHSUNDEEP
sql-server
MertYılmaz
MertYılmaz

Reputation: 55

T sql group by month

I'm trying to group by according to month from datetime

I run below query

select  cf.flow_name as 'Process', COUNT(c.case_ID) as 'Case', CONVERT(VARCHAR(10),c.xdate,104) as 'Date' 
from cases c inner join case_flow cf  on c.case_flow_ID=cf.CF_ID 
where    project_ID=1 and c.subject_ID=1
group by cf.flow_name,c.xdate

Columns data types as below

flow_name varchar(100)

case_ID int

xdate datetime

Result displays like below if i run above query

Process - Case - Date

Test      1  30.01.2015
Test      1  30.01.2015
analysis  1  19.03.2015
analysis  1  30.03.2015
analysis  1  13.04.2015
analysis  1  16.04.2015

Question:

I need to group by as below (group by according to month for x.date)

Correct Result should be as below

Process - Case - Date

Test      2  30.01.2015 (Because Test has 2 data from 01 month)
analysis  2  19.03.2015 (Because analysis has 2 data from 03 month)
analysis  2  13.04.2015 (Because analysis has 2 data from 04 month)

as above all result should group by month how can i do this according to my query ?

hope you understand my english thanks

Upvotes: 0

Views: 414

Answers (1)

Z .
Z .

Reputation: 12837

SELECT cf_flow, 
       Count(*), 
       Min(xdate) 
FROM   cases c 
       INNER JOIN case_flow cf 
               ON c.case_flow_id = cf.cf_id 
WHERE  project_id = 1 
       AND c.subject_id = 1 
GROUP  BY cf_flow, 
          Dateadd(month, Datediff(month, 0, xdate), 0)

Upvotes: 2

Related Questions