很长一段时间听者中，第一次调用者。 我使用Crystal Reports 2010。
我有我需要组合在一起，如果体积不改变每日交易信息。 下面是数据的模样。
贸易＃BegDate结束日期卷
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
我需要它看起来像这样。
贸易＃DATERANGE卷
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
我需要由派生日期范围组，但我不知道如何与水晶做到这一点。 有任何想法吗？？
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
Answer 2:
我会使用“X-2”抑制功能隐藏所有，但最后的每一行，和上一个（）和Next（）来查找端点。
集团通过BegDate; 抑制细节和组页脚部分。
在组标题某处创建{@UpdateCurrentBegDate}功能
WhilePrintingRecords;
Global DateVar CurrentBegDate;
If PreviousIsNull({table.Volume}) or Previous({table.Volume}) <> {table.Volume}
Then ( //We found a new range
CurrentBegDate = {table.BegDate};
);
""; //Display nothing on screen
并正在显示一个{} @DisplayBegDate功能，你现在有BegDate
EvaluateAfter({@UpdateCurrentBegDate});
Global DateVar CurrentBegDate;
前往科专家，点击旁边的禁止选项（组页眉节）“X-2”。 这是因为在那里有式
Not(NextIsNull({table.Volume}) or Next({table.Volume}) <> {table.Volume})
如果你需要做的总计或其它计算，你会做它在{} @UpdateCurrentBegDate功能（和你想要的名称更改为{} @UpdateCurrentValues或类似的东西）。 您还可以创建，如果你只是想改变的东西当组改变了检查下一个（）信息的新功能 - 使用默认共计功能将包括隐藏价值。
Answer 3:
with w as (
select 1 id, to_date('1/1/2012', 'mm/dd/yyyy') db, to_date('1/2/2012', 'mm/dd/yyyy') de, 500 s 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'), 510 from dual
)
select tmin.db, tmax.de, tmin.s
from
(
select
row_number() over (order by db) id,
db,
s
from
(
select
db,
s,
case
when ps is null
then 1
when ps != s
then row_number() over (order by db)
else 0 end num
from (
select
(db)
, (de)
, lag (s,1) over (ORDER BY db) ps
, s
from w
) t
) t1
where num != 0
) tmin,
(select
row_number() over (order by db) id,
de,
s
from
(
select
db,
de,
s,
case
when ps is null
then 1
when ps != s
then row_number() over (order by de desc)
else 0 end num
from (
select
db
,(de)
, lag (s,1) over (ORDER BY de desc) ps
, s
from w
order by db
) t
) t1
where num != 0) tmax
where tmin.id = tmax.id
Answer 4:
这是最完美的解决方案，我可以拿出
WITH DirectTrades(tradeid, SourceDate, EndDate, Volume, Row, KillRow, Depth) AS
(
SELECT tradeid
BegDate AS SourceDate,
EndDate,
Volume,
ROW_NUMBER() over (partition by Table# order by BegDate) AS Row,
ROW_NUMBER() over (partition by Table# order by BegDate) AS KillRow,
0 AS Depth
FROM Trade
UNION ALL
SELECT t1.Tradeid
dt.SourceDate,
t1.EndDate,
t1.Volume,
dt.Row,
dt.Row + dt.Depth + 1,
dt.Depth + 1
FROM Trade AS t1
INNER JOIN
DirectTrades AS dt ON
t1.BegDate=dt.EndDate AND
t1.Volume=dt.Volume AND
t1.tradeid=dt.Tradeid
)
SELECT dt1.Tradeid
dt1.SourceDate,
dt1.EndDate,
dt1.Volume
FROM DirectTrades dt1
INNER JOIN
(
SELECT dt2.Row,
MAX(dt2.KillRow) AS KillRow
FROM DirectTrades dt2
WHERE dt2.Row NOT IN
(
SELECT dt3.KillRow
FROM DirectTrades dt3
WHERE dt3.Depth <> 0
)
GROUP BY dt2.Row
) dt4 ON dt1.Row=dt4.Row AND dt1.KillRow=dt4.KillRow
ORDER BY SourceDate
文章来源: Crystal Reports Need to Group by Derived Date Range
