Hi Tom,
I have the below requirement
Table:
create table test1
(start_date date,
amount number)
insert into test1 values('18-jan-17', 27000);
insert into test1 values('19-jan-17', 27000);
insert into test1 values('20-jan-17', 30000);
insert into test1 values('21-jan-17', 30000);
insert into test1 values('22-jan-17', 30000);
insert into test1 values('23-jan-17', 29000);
insert into test1 values('24-jan-17', 29000);
insert into test1 values('25-jan-17', 30000);
insert into test1 values('26-jan-17', 30000);
commit;
START_DATE AMOUNT
18-JAN-17 00:00:00 27000
19-JAN-17 00:00:00 27000
20-JAN-17 00:00:00 30000
21-JAN-17 00:00:00 30000
22-JAN-17 00:00:00 30000
23-JAN-17 00:00:00 29000
24-JAN-17 00:00:00 29000
25-JAN-17 00:00:00 30000
26-JAN-17 00:00:00 30000Now desired output is
START_DATE END_DATE
18-JAN-17 19-JAN-17
20-JAN-17 22-JAN-17
23-JAN-17 24-JAN-17
25-JAN-17 26-JAN-17
I tried the below query
WITH tab AS
(SELECT rownum rn, a.* FROM test1 a ORDER BY start_date
)
SELECT *
FROM
(SELECT x.start_date ,
CASE
WHEN x.amount = y.amount
THEN y.start_date
END AS end_date
FROM tab x,
tab y
WHERE x.rn+1 = y.rn(+)
)
WHERE end_date IS NOT NULL.but my output is looking like
START_DATE END_DATE
18-JAN-17 00:00:00 19-JAN-17 00:00:00
20-JAN-17 00:00:00 21-JAN-17 00:00:00 --> THIS ROW IS NOT DESIRED (instead it should be 20-jan-17 22-jan-17)
21-JAN-17 00:00:00 22-JAN-17 00:00:00
23-JAN-17 00:00:00 24-JAN-17 00:00:00
25-JAN-17 00:00:00 26-JAN-17 00:00:00
Please help me with the query
Thanks
Arun
What exactly is your logic? Find the start and end dates of a consecutive period of days with the same amount?
If so, the Tabibitosan method is your friend:
WITH tab AS
(SELECT a.*,
row_number() over (order by start_date) -
row_number() over (partition by amount order by start_date) grp
FROM test1 a
)
select min(start_date), max(start_date), amount from tab
group by grp, amount
order by 1;
MIN(START_DATE) MAX(START_DATE) AMOUNT
18-JAN-0017 00:00:00 19-JAN-0017 00:00:00 27000
20-JAN-0017 00:00:00 22-JAN-0017 00:00:00 30000
23-JAN-0017 00:00:00 24-JAN-0017 00:00:00 29000
25-JAN-0017 00:00:00 26-JAN-0017 00:00:00 30000