Grouping Date Intervals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Date Intervals

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-08-04 : 08:32:56

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...

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-08-04 : 09:45:37

Solution found:


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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources