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)
 Date Field By Bucket

Author  Topic 

evanburen
Posting Yak Master

167 Posts

Posted - 2014-05-16 : 09:05:33
Hi

I'm looking to get a count of the number of records which fall into each bucket by the field 'DateResolved'. I want to get count where the DateResolved was within 1-7 days of the current date, then 8-14 days. Thanks

SELECT 
COUNT(IssueID) As IssueCountTotal
,SUM(CASE WHEN (DateResolved > DATEADD(d, -1, GETDATE()) AND DateResolved > DATEADD(d, -7, GETDATE())) THEN 1 ELSE 0 END) AS [1-7 Days]
,SUM(CASE WHEN (DateResolved >= DATEADD(d, -8, GETDATE()) AND DateResolved > DATEADD(d, -14, GETDATE())) THEN 1 ELSE 0 END) AS [8-14 Days]
FROM
Issues

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-16 : 10:05:31
You would need < or <= in the first comparison in the WHEN expression.
SELECT 
COUNT(IssueID) As IssueCountTotal
,SUM(CASE WHEN (DateResolved <= DATEADD(d, -1, GETDATE()) AND DateResolved > DATEADD(d, -7, GETDATE())) THEN 1 ELSE 0 END) AS [1-7 Days]
,SUM(CASE WHEN (DateResolved <= DATEADD(d, -8, GETDATE()) AND DateResolved > DATEADD(d, -14, GETDATE())) THEN 1 ELSE 0 END) AS [8-14 Days]
FROM
Issues
Another thing to keep in mind is that when you use GETDATE() it includes the time portion also. If DateResolved also has the time portion, then your comparison would include the time. If it does not include time, then a comparison such as DateResolved > DATEADD(d, -7, GETDATE()) may exclude one day that you didn't want to exclude. If DateResolved does not have the time portion, change the GETDATE() to CAST(GETDATE() AS DATE), and then experiment with < or <= to get the right data according to your requirements.
Go to Top of Page

evanburen
Posting Yak Master

167 Posts

Posted - 2014-05-16 : 10:28:45
Thanks again, James
Go to Top of Page
   

- Advertisement -