SQLTeam.com | Weblogs | Forums

Unable to calculate count based on two different dates

tsql

#1

.i'm using below query, this gives assigned count properly since i'm grouping bases on created date but i want to group for both closed and created date in order to get assigned and closed count which i'm unable to.I'm looking someone to help to get counts based on the dates for each.

SELECT
DATENAME(month,I.DateCreated) as Month,
AssignedCNT = SUM (CASE WHEN I.Status = 'Assigned' THEN 1 ELSE 0 END),
ClosedCNT = SUM(CASE WHEN I.Status = 'Closed' THEN 1 ELSE 0 END),
FROM IM_V_Defects I
WHERE I.ProjectName = 'Corporate' I.DateLastMod >='01/01/2017' and I.DateCreated >='01/01/2017'
GROUP BY DATEPART(month,I.DateCreated),DATEName(month,I.DateCreated)
ORDER BY DATEPART(month,I.DateCreated)


#2

Please post consumable SQL with create table or declare table variable with inserts of sample data. You posted what you tried, thanks.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx