# SQL - grouping by specific date ranges.
**URL:** https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194
**Category:** Factual Questions
**Created:** [June 23, 2010, 1:25am UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194 "2010-06-23T01:25:18Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 23, 2010, 1:25am UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194/1 "2010-06-23T01:25:18Z")
Given a set of date ranges (i.e. financial period dates) that inconveniently do not begin at useful points (such as the first of a month, or the last of a month, or a specific number of weeks) is it possible to group by these date ranges in a sql query?
Date ranges in question…
```auto
P start end
1 01/06/2009 28/06/2009
2 29/06/2009 26/07/2009
3 27/07/2009 30/08/2009
4 31/08/2009 27/09/2009
5 28/09/2009 25/10/2009
6 26/10/2009 29/11/2009
7 30/11/2009 27/12/2009
8 28/12/2009 24/01/2010
9 25/01/2010 28/02/2010
10 01/03/2010 28/03/2010
```
I have done it using case statements as follows…
```auto
select
sum(case when dbdate between '09/06/01' and '09/06/28' then handle else 0 end) as period1,
sum(case when dbdate between '09/06/29' and '09/07/26' then handle else 0 end) as period2,
sum(case when dbdate between '09/07/27' and '09/08/30' then handle else 0 end) as period3,
sum(case when dbdate between '09/08/31' and '09/09/27' then handle else 0 end) as period4,
sum(case when dbdate between '09/09/28' and '09/10/25' then handle else 0 end) as period5,
sum(case when dbdate between '09/10/26' and '09/11/29' then handle else 0 end) as period6,
sum(case when dbdate between '09/11/30' and '09/12/27' then handle else 0 end) as period7,
sum(case when dbdate between '09/12/28' and '10/01/24' then handle else 0 end) as period8,
sum(case when dbdate between '10/01/25' and '10/02/28' then handle else 0 end) as period9,
sum(case when dbdate between '10/03/01' and '10/03/28' then handle else 0 end) as period10
from accountactivity
where dbdate between '09/06/01' and '10/03/28'
```
but this puts the figures as columns all on one row. And is surely not the best way to do this type of thing.
Is there a more ‘elegant’ or sensible way to acheive the result (that produces rows of data instead of one row with columns)
edit: the date is a text field in the format ‘yy/mm/dd’… don’t ask.
---
### Author:  [@AnalogSignal](https://boards.straightdope.com/u/AnalogSignal)
#### Post date: [June 23, 2010, 2:02am UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194/2 "2010-06-23T02:02:02Z")
Yes, you can get the results you want. I am on a netbook now without access to a database but this is roughly the SQL Server syntax for it. Note you will need to repeat the entire CASE statement in the GROUP BY clause without a column alias.
select
case
when dbdate between ‘09/06/01’ and ‘09/06/28’ then ‘period1’
when dbdate between ‘09/07/27’ and ‘09/08/30’ then ‘period2’
when dbdate between ‘09/08/31’ and ‘09/09/27’ then ‘period3’
.
.
.
end as Period,
sum(handle) as handle
from accountactivity
where dbdate between ‘09/06/01’ and ‘10/03/28’
group by
case
when dbdate between ‘09/06/01’ and ‘09/06/28’ then ‘period1’
when dbdate between ‘09/07/27’ and ‘09/08/30’ then ‘period2’
when dbdate between ‘09/08/31’ and ‘09/09/27’ then ‘period3’
.
.
.
end
---
### Author:  [@Ruminator](https://boards.straightdope.com/u/Ruminator)
#### Post date: [June 23, 2010, 2:07am UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194/3 "2010-06-23T02:07:03Z")
Option 1:
Move your CASE statements to the GROUP BY clause
Option 2:
Write a User Defined Function()
Option 3:
Create a 2nd table (a lookup table) that represents your date ranges and rewrite your SQL to join the tables.
---
### Author:  [@Lobsang](https://boards.straightdope.com/u/Lobsang)
#### Post date: [June 23, 2010, 2:30am UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194/4 "2010-06-23T02:30:48Z")
> [@Ruminator](#):
>
> Option 1:
>
> Move your CASE statements to the GROUP BY clause
> Option 2:
>
> Write a User Defined Function()
> Option 3:
>
> Create a 2nd table (a lookup table) that represents your date ranges and rewrite your SQL to join the tables.
When I saw your option 1 a lightbulb above my head switched on. I’ve done this before. All the time I was doing this I was thinking “Something about having the case statement repeated in the group by and having the first case also in the select list with strings blah blah”
Anyway this acheived what I was looking for. Thanks **Ruminator**! … for pointing me towards the solution and helping improve my sql abilities 🙂
edit…
```auto
select case when _date between '09/06/01' and '09/06/28' then 'period1'
when _date between '09/06/29' and '09/07/26' then 'period2'
when _date between '09/07/27' and '09/08/30' then 'period4'
when _date between '09/08/31' and '09/09/27' then 'period5'
when _date between '09/09/28' and '09/10/25' then 'period6'
when _date between '09/10/26' and '09/11/29' then 'period7'
when _date between '09/11/30' and '09/12/27' then 'period8'
when _date between '09/12/28' and '10/01/24' then 'period9'
when _date between '10/01/25' and '10/02/28' then 'period10'
when _date between '10/03/01' and '10/03/28' then 'period11' else 'other' end as period,
sum(handle)
from accountactivity
where _date between '09/06/01' and '10/03/28'
group by
case when _date between '09/06/01' and '09/06/28' then 'period1'
when _date between '09/06/29' and '09/07/26' then 'period2'
when _date between '09/07/27' and '09/08/30' then 'period4'
when _date between '09/08/31' and '09/09/27' then 'period5'
when _date between '09/09/28' and '09/10/25' then 'period6'
when _date between '09/10/26' and '09/11/29' then 'period7'
when _date between '09/11/30' and '09/12/27' then 'period8'
when _date between '09/12/28' and '10/01/24' then 'period9'
when _date between '10/01/25' and '10/02/28' then 'period10'
when _date between '10/03/01' and '10/03/28' then 'period11' else 'other' end
order by period
```
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [June 23, 2010, 3:17pm UTC](https://boards.straightdope.com/t/sql-grouping-by-specific-date-ranges/544194/5 "2010-06-23T15:17:12Z")
> [@Ruminator](#):
>
> Option 1:
>
> Move your CASE statements to the GROUP BY clause
> Option 2:
>
> Write a User Defined Function()
> Option 3:
>
> Create a 2nd table (a lookup table) that represents your date ranges and rewrite your SQL to join the tables.
Just to put in my $.02: I always use Option 3. But that’s because IME, there’s no such thing as a one-off ad hoc grouping for a report. If someone needs a grouping like this today, they’ll end up looking for the same grouping on another report from a different data table later on, trying to compare numbers or something. It’s easier to reuse the code when it’s not a long CASE statement, and it’s easier to update the date ranges (eg. adding a new one) when you just have to add another row or fifty to a lookup table than to go through all your saved queries and views to update all of the SQL code.