sql - Crystal Reports Need to Group by Derived Date Range

Question

Long time listner, first time caller. I'm using Crystal Reports 2010.

I have daily trade information that I need to group together if the volume doesn't change. Here's what the data looks like.

Trade# BegDate EndDate Volume

1        1/1/2012    1/2/2012    500
1        1/2/2012    1/3/2012    500
1        1/3/2012    1/4/2012    1000
1        1/4/2012    1/5/2012    750
1        1/5/2012    1/6/2012    750
1        1/6/2012    1/7/2012    500
1        1/7/2012    1/8/2012    500
1        1/8/2012    1/9/2012    500

I need it to look like this.

Trade# DateRange Volume

1        1/1/2012 - 1/3/2012  500
1        1/3/2012 - 1/4/2012  1000
1        1/4/2012 - 1/6/2012  750
1        1/6/2012 - 1/9/2012  500

I need to group by the derived date ranges but I'm not sure how to accomplish this with Crystal. Any ideas??

Answer 1

with w as (
 select 1 id, to_date('1/1/2012', 'mm/dd/yyyy') start_date, to_date('1/2/2012', 'mm/dd/yyyy') end_date, 500 sales_volume from dual
 union all
 select 1, to_date('1/2/2012', 'mm/dd/yyyy'), to_date('1/3/2012', 'mm/dd/yyyy'), 500 from dual
 union all
 select 1, to_date('1/3/2012', 'mm/dd/yyyy'), to_date('1/4/2012', 'mm/dd/yyyy'), 1000 from dual
 union all
 select 1, to_date('1/4/2012', 'mm/dd/yyyy'), to_date('1/5/2012', 'mm/dd/yyyy'), 750 from dual
 union all
 select 1, to_date('1/5/2012', 'mm/dd/yyyy'), to_date('1/6/2012', 'mm/dd/yyyy'), 750 from dual
 union all
 select 1, to_date('1/6/2012', 'mm/dd/yyyy'), to_date('1/7/2012', 'mm/dd/yyyy'), 500 from dual
 union all
 select 1, to_date('1/7/2012', 'mm/dd/yyyy'), to_date('1/8/2012', 'mm/dd/yyyy'), 500 from dual
 union all
 select 1, to_date('1/8/2012', 'mm/dd/yyyy'), to_date('1/9/2012', 'mm/dd/yyyy'), 500 from dual      
 )

,t as (select sales_volume
             ,start_date
             ,end_date 
             ,lag (sales_volume,1) over (order by start_date) prev_sales_volume
       from w
       order by start_date)
,u as (select *
       from t 
       where nvl(prev_sales_volume,-1) != sales_volume
       order by start_date)
select start_date
      ,nvl(lead (start_date,1) over (order by start_date),(select max(end_date) from w))   end_date 
      ,sales_volume
from  u
order by start_date