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