SQL Grouping and DATEDIFF Issue

The query below is using Axosoft's OnTime DB with a few custom fields. If you ignore the custom fields and have some sample data this should work.

What I'm trying to do:

Return a query that has a count(total) number of open tickets that have been open during these time frames:

• Less than a day
• 1 Day
• 2 Days
• 3 Days
• 4 Days
• 5 Days
• 6 Days
• More than a week

this is for a ticket aging query. Here's the query below:

DECLARE @endDate DateTime;
SET @endDate = '03/18/2011';

WITH
      WorkItems AS
      (
            SELECT
                  i.ProjectID AS ProjectID,
                  CASE WHEN ic.Custom_279 = 'Bug' THEN 'Issue' WHEN ic.Custom_279 IS NULL THEN 'Other' WHEN LTRIM(RTRIM(ic.Custom_279)) = '' THEN 'Other' ELSE ic.Custom_279 END  AS WorkItemType,
                  i.IncidentNumber AS ID,
                  i.Name AS Name,
                  --CASE WHEN ic.Custom_264 < '1901-01-01' THEN NULL ELSE ic.Custom_264 END AS DateReported,
                  CASE WHEN i.CreatedDateTime < '1901-01-01' THEN NULL ELSE i.CreatedDateTime END AS DateReported,
                  CASE WHEN ic.Custom_265 < '1901-01-01' THEN NULL ELSE ic.Custom_265 END AS DateResolutionBegan,
                  CASE WHEN ic.Custom_266 < '1901-01-01' THEN NULL ELSE ic.Custom_266 END AS DateSignoffRequested,
                  CASE WHEN ic.Custom_267 < '1901-01-01' THEN NULL ELSE ic.Custom_267 END AS DateClosed             
            FROM
                  dbo.Incidents AS i
                        INNER JOIN dbo.IncidentCustomFields AS ic ON ic.IncidentID = i.IncidentID
            WHERE
                  i.Archived = 0
      ),
      ProjectDescendantsIncludingSelf AS
      (
            SELECT
                    p.ProjectID AS ProjectID,
                    p.ProjectID AS DescendantProjectID,
                    CAST('/' + p.Name AS NVARCHAR) AS ProjectPath
            FROM
                    dbo.Projects AS p
            UNION ALL
            SELECT
                    pd.ProjectID AS ProjectID,
                    p.ProjectID AS DescendantProjectID,
                    CAST(pd.ProjectPath + N'/' + p.Name AS NVARCHAR) AS ProjectPath
            FROM
                    ProjectDescendantsIncludingSelf AS pd
                              INNER JOIN dbo.Projects AS p ON p.ParentID = pd.DescendantProjectID                        
      ),
    OpenTicketsLessThanDay AS   
        (SELECT 
            COUNT(ID) AS [LessThanDayTicketCount],
            CASE WHEN DATEDIFF(DAY, DateReported, @endDate) < 1 THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [LessThanDay],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 1) AND (DATEDIFF(DAY, DateReported, @endDate) <= 2) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan1Day],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 2) AND (DATEDIFF(DAY, DateReported, @endDate) <= 3) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan2Days],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 3) AND (DATEDIFF(DAY, DateReported, @endDate) <= 4) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan3Days],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 4) AND (DATEDIFF(DAY, DateReported, @endDate) <= 5) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan4Days],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 5) AND (DATEDIFF(DAY, DateReported, @endDate) <= 6) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan5Days],
            --CASE WHEN (DATEDIFF(DAY, DateReported, @endDate) > 6) AND (DATEDIFF(DAY, DateReported, @endDate) <= 7) THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThan6Days],
            --CASE WHEN DATEDIFF(WEEK, DateReported, @endDate) > 1 THEN DATEDIFF(DAY, DateReported, @endDate) ELSE NULL END AS [GreaterThanWeek],
            --DateReported,
            --DateClosed, 
            --d.ProjectPath

    FROM WorkItems wi
        INNER JOIN ProjectDescendantsIncludingSelf d ON d.DescendantProjectID = wi.ProjectID
    WHERE 
        DateReported < @endDate AND
        (DateClosed IS NULL OR DateClosed > @endDate) AND
        d.ProjectID = 182 AND
        d.DescendantProjectID != 185
    GROUP BY LessThanDay

    )

SELECT [LessThanDayTicketCount] FROM OpenTicketsLessThanDay
GROUP开发者_StackOverflow社区 BY LessThanDay
ORDER BY LessThanDay ASC

I presume you need something like one or the other of these.

DECLARE @EndDate datetime = getdate()

SELECT 
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-1,@EndDate)  THEN 1 END) AS [LessThanDay],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-2,@EndDate) AND modify_date <= DATEADD(DAY,-1,@EndDate) THEN 1 END) AS [GreaterThan1Day],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-3,@EndDate) AND modify_date <= DATEADD(DAY,-2,@EndDate) THEN 1 END) AS [GreaterThan2Days],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-4,@EndDate) AND modify_date <= DATEADD(DAY,-3,@EndDate) THEN 1 END) AS [GreaterThan3Days],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-5,@EndDate) AND modify_date <= DATEADD(DAY,-4,@EndDate) THEN 1 END) AS [GreaterThan4Days],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-6,@EndDate) AND modify_date <= DATEADD(DAY,-5,@EndDate) THEN 1 END) AS [GreaterThan5Days],
      COUNT(CASE WHEN modify_date > DATEADD(DAY,-7,@EndDate) AND modify_date <= DATEADD(DAY,-6,@EndDate) THEN 1 END) AS [GreaterThan6Days],
      COUNT(CASE WHEN modify_date <= DATEADD(DAY,-7,@EndDate) THEN 1 END) AS [GreaterThanWeek]
FROM sys.objects
WHERE modify_date <= @EndDate


SELECT 
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 0  THEN 1 END) AS [LessThanDay],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 1  THEN 1 END) AS [GreaterThan1Day],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 2  THEN 1 END) AS [GreaterThan2Days],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 3  THEN 1 END) AS [GreaterThan3Days],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 4  THEN 1 END) AS [GreaterThan4Days],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 5  THEN 1 END) AS [GreaterThan5Days],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) = 6  THEN 1 END) AS [GreaterThan6Days],
      COUNT(CASE WHEN DATEDIFF(DAY, modify_date, @EndDate) > 6  THEN 1 END) AS [GreaterThanWeek]
FROM sys.objects
WHERE modify_date <= @EndDate