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

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.

evanburen
Posting Yak Master

167 Posts

Posted - 2014-05-16 : 10:28:45

Thanks again, James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources