长时间列表,第一次来电.我正在使用Crystal Reports 2010.
我有每日交易信息,如果交易量不变,我需要将它们组合在一起.这是数据的样子.
交易#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
