sql – Crystal Reports需要按派生日期范围分组

我有每日交易信息,如果交易量不变,我需要将它们组合在一起.这是数据的样子.

交易#BegDate EndDate卷

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

我需要它看起来像这样.

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

我需要按派生日期范围进行分组,但我不确定如何使用Crystal完成此操作.有任何想法吗？？

解决方法

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,'mm/dd/yyyy'),to_date('1/3/2012',500 from dual
 union all
 select 1,to_date('1/4/2012',1000 from dual
 union all
 select 1,to_date('1/5/2012',750 from dual
 union all
 select 1,to_date('1/6/2012',to_date('1/7/2012',to_date('1/8/2012',to_date('1/9/2012',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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!