Every friday we have a meeting and we have a report which shows us the number of incidents created closed open in the last week and the last 30 days. I have most of the query...but some how i missed the closed column and can't back track my self...can someone help. The Query is below!!



DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @STLYStartDate datetime
DECLARE @STLYEndDate datetime
SET @StartDate = CONVERT(VARCHAR(30),Getdate(),101)
SET @EndDate = DATEADD(DAY, 0, @StartDate)
SET @StartDate = DATEADD(DAY, - 7, @EndDate)
SET @STLYEndDate = @StartDate
SET @STLYStartDate = DATEADD(DAY,-7,@STLYEndDate)


SELECT GROUPS.GROUP_NAME,
SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @StartDate and @EndDate
THEN 1 ELSE 0 END )AS CREATED,
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [OPEN],
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NOT NULL THEN 0 ELSE 0 END) AS [CLOSED],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-30, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-90, @EndDate)
THEN 1 ELSE 0 END) AS [OPEN90],
/*COUNT(LastINCIDENTS.DT_CREATED)*/
SUM( CASE WHEN INCIDENTS.DT_CREATED BETWEEN @STLYStartDate and @STLYEndDate
THEN 1 ELSE 0 END )AS STLYCREATED,
SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL THEN 1 ELSE 0 END) AS [STLYOPEN],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day,-30, @STLYEndDate)
THEN 1 ELSE 0 END) AS [STLYOPEN30],

SUM(CASE WHEN INCIDENTS.DT_CLOSED IS NULL AND DT_CREATED<DATEADD(day, -90,@STLYEndDate)
THEN 1 ELSE 0 END) AS [STLYOPEN90]


FROM GROUPS INNER JOIN
INCIDENTS ON INCIDENTS.ID_GROUP = GROUPS.ID
INNER JOIN
REPS ON INCIDENTS.ID_ASSIGNEE = REPS.ID
WHERE GROUPS.GROUP_NAME LIKE 'MAC%' AND GROUPS.GROUP_NAME NOT LIKE 'MAC
RENT%'

GROUP BY GROUP_NAME ORDER BY GROUP_NAME