SQL How to group by date ranges

Question

Can someone help me sort out a group by dates?

Right now I have a query similar to this

Select date, count(x) from data group by date 

This returns results that look like

  2011/1/1 10 2011/1/2 5 2011/1/3 8 2011/1/4 3 

etc...

But I would like to recount every 2 days so that the data looks like this:

  2011/1/1 15 2011/1/3 11 

Any ideas?

thanks

Answer 1

You can normalize dates to groups of 2 by converting them to a numeric integer value and decreasing to even numbers. A simple way to do this is: val / 2 * 2 , because the first / 2 will be truncated to any decimal places (if the type val is an integer!), A * 2 will return it to its original value, from normalized to even. Here is an example that normalizes and groups results using a CTE data source:

 ;with Data as ( select '1/1/2011' as [date], 1 as x union select '1/1/2011' as [date], 2 as x union select '1/1/2011' as [date], 3 as x union select '1/1/2011' as [date], 4 as x union select '1/1/2011' as [date], 5 as x union select '1/1/2011' as [date], 6 as x union select '1/1/2011' as [date], 7 as x union select '1/1/2011' as [date], 8 as x union select '1/1/2011' as [date], 9 as x union select '1/1/2011' as [date], 10 as x union select '1/2/2011' as [date], 11 as x union select '1/2/2011' as [date], 12 as x union select '1/2/2011' as [date], 13 as x union select '1/2/2011' as [date], 14 as x union select '1/2/2011' as [date], 15 as x union select '1/3/2011' as [date], 16 as x union select '1/3/2011' as [date], 17 as x union select '1/3/2011' as [date], 18 as x union select '1/3/2011' as [date], 19 as x union select '1/3/2011' as [date], 20 as x union select '1/3/2011' as [date], 21 as x union select '1/3/2011' as [date], 22 as x union select '1/3/2011' as [date], 23 as x union select '1/4/2011' as [date], 24 as x union select '1/4/2011' as [date], 25 as x union select '1/4/2011' as [date], 26 as x ) Select cast(cast(cast(Date as datetime) as integer) / 2 * 2 as datetime) as date, count(x) from data group by cast(cast(Date as datetime) as integer) / 2 * 2 

Output:

 date (No column name) 2011-01-01 00:00:00.000 15 2011-01-03 00:00:00.000 11 

Answer 2

 Select floor((date - trunc(date,'MM')) / 2), count(x) from data group by floor((date - trunc(date,'MM')) / 2)