Crystal Reports需要按派生日期范围进行分组 [英] Crystal Reports Need to Group by Derived Date Range
本文介绍了Crystal Reports需要按派生日期范围进行分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果交易量没有变化,我每天都需要交易信息。这里是数据的样子。
交易#结束日期交易量
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
我需要它看起来像这样。
交易#日期范围交易量
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
union all
选择1,to_date('1/2/2012','mm / dd / yyyy'),to_date('1/3/2012','mm / dd / yyyy'),500 from double
union all
选择1,to_date('1/3/2012','mm / dd / yyyy'),to_date('1/4/2012','mm / dd / yyyy'),1000 from double
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 double
union all
select 1,to_date('1/7/2012','mm / dd / yyyy'),to_date('1 / 8/2012',' mm / dd / yyyy'),500 from double
union all
select 1,to_date('1/8/2012','mm / dd / yyyy'),to_date('1/9 / 2012年','mm / dd / yyyy'),500从双
)
,t(选择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(按start_date排序),(select max(end_date)from w))end_date
,sales_volume
from
order by start_date
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??
解决方案
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
这篇关于Crystal Reports需要按派生日期范围进行分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文