CODEWITHSUNDEEP
sql
Zath
Zath

Reputation: 557

Date Interval Counts

I have been searching around for an example on how to group the count of date intervals that are exclusive.

One way it can be done is this way:

 DECLARE @tmp TABLE(cnt INT)

 INSERT INTO @tmp(cnt)
 SELECT COUNT(*) AS ProblemCnt
 FROM Problems
 WHERE Closed IS NULL
 AND (DATEDIFF(DAY, Opened, GETDATE()) <=10)

 INSERT INTO @tmp(cnt)
 SELECT COUNT(*) AS ProblemCnt
 FROM Problems
 WHERE Closed IS NULL
 AND (DATEDIFF(DAY, Opened, GETDATE()) >10
    AND DATEDIFF(DAY, Opened, GETDATE()) <=20)

 INSERT INTO @tmp(cnt)
 SELECT COUNT(*) AS ProblemCnt
 FROM Problems
 WHERE Closed IS NULL
 AND (DATEDIFF(DAY, Opened, GETDATE()) >20
     AND DATEDIFF(DAY, Opened, GETDATE()) <=30)

 SELECT cnt FROM @tmp

Problem is with the way it's done above is it is not as dynamic as needed and it is needed to go up to 300 days. So, there would be a lot of selects and inserts. Not very proficient.

Also, I have been looking at something like this:

 dateadd(day, 5 + (datediff(day, 0, Opened) / 5) * 5, 0)

This is just a sample of a possibility that needs adjustment.

Does anyone have a better suggestion on how to achieve this without using the temp table above?

Thanks...

Upvotes: 0

Views: 48

Answers (2)

GarethD
GarethD

Reputation: 69749

You could round the difference in days down (or up) to the nearest 10, then group by this interval:

SELECT  FLOOR(DATEDIFF(DAY, Opened, GETDATE()) / 10.0) * 10 AS Interval,
        COUNT(*) AS ProblemCnt
FROM    Problems
WHERE   Closed IS NULL
AND     Opened >= DATEADD(DAY, -300, CAST(GETDATE() AS DATE))
AND     Opened < GETDATE()
GROUP BY FLOOR(DATEDIFF(DAY, Opened, GETDATE()) / 10.0) * 10;

I have also changed your where clause slightly, your current WHERE clause is not sargable. e.g. using 

WHERE   DATEDIFF(DAY, Opened, GETDATE()) < 10

Means that the function is evaluated for every record, and any indexes on Opened can't be used, but switching this to 

WHERE   Opened >= DATEADD(DAY, -300, CAST(GETDATE() AS DATE))
AND     Opened < GETDATE()

Means that DATEADD is evaluated once at the start of the query (since GETDATE() is treated as a constant), and because Opened is being compared directly indexes can now be used.

Upvotes: 2

Surendra
Surendra

Reputation: 721

it can be done with a simple loop as below 

DECLARE @tmp TABLE(cnt INT)

DECLARE @T INT 
SET @T = 0

WHILE (@T <=290)
BEGIN

    INSERT INTO @tmp(cnt)
    SELECT COUNT(*) AS ProblemCnt
    FROM Problems
    WHERE Closed IS NULL
    AND (DATEDIFF(DAY, Opened, GETDATE()) >@T
       AND DATEDIFF(DAY, Opened, GETDATE()) <=@T + 10)

    SET @T = @T + 10

END

SELECT cnt FROM @tmp

Upvotes: 1

Related Questions