You Asked
Hi Tom,
Please disregard my previous ticket as I am not able to edit and the query requirement has changed. Thanks for the link by I am struggling a bit.
I have a table of daily rates for a service that contains a price and an Y/N field to stop sale of any particular service on any given date.
create table t_rates
(
r_rate_id number(9),
r_date date,
r_rate number(12,2),
r_stop_sale varchar2(1 CHAR)
);
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '01-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '02-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '03-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '04-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '05-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '06-SEP-2015',90.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '07-SEP-2015',90.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '08-SEP-2015',90.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '09-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '10-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '11-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '12-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '13-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '14-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '15-SEP-2015',100.00,'Y');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '16-SEP-2015',100.00,'Y');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '17-SEP-2015',100.00,'Y');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '18-SEP-2015',100.00,'Y');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '19-SEP-2015',100.00,'N');
insert into t_rates (r_rate_id,r_date,r_rate,r_stop_sale) values (1, '20-SEP-2015',100.00,'N');
select *
from t_rates
order by r_rate_id, r_date;
R_RATE_ID R_DATE R_RATE R_STOP_SALE
--------- --------- ---------- -----------
1 01-SEP-15 100 N
1 02-SEP-15 100 N
1 03-SEP-15 100 N
1 04-SEP-15 100 N
1 05-SEP-15 100 N
1 06-SEP-15 90 N
1 07-SEP-15 90 N
1 08-SEP-15 90 N
1 09-SEP-15 100 N
1 10-SEP-15 100 N
1 11-SEP-15 100 N
1 12-SEP-15 100 N
1 13-SEP-15 100 N
1 14-SEP-15 100 N
1 15-SEP-15 100 Y
1 16-SEP-15 100 Y
1 17-SEP-15 100 Y
1 18-SEP-15 100 Y
1 19-SEP-15 100 N
1 20-SEP-15 100 N
The desired results of the query would be:
r_rate_id r_start_date r_end_date r_rate r_stop_sale
1 01-SEP-2015 05-SEP-2015 100 N
1 06-SEP-2015 07-SEP-2015 90 N
1 09-SEP-2015 14-SEP-2015 100 N
1 15-SEP-2015 18-SEP-2015 100 Y
1 19-SEP-2015 20-SEP-2015 100 N
As you can see, the query would group by r_rate_id for a date range where both the rate and stop sale flag are the same, otherwise a new date range group is created.
In actuality, there are several more fields that will be considered for the distinct date range grouping but if you can get me going on the initial query I can take it from there (hopefully) to add the others in as needed.
Thanks in advance, sorry for the duplicate ticket.
and Connor said...
OK, lets build it up...
"where both the rate and stop sale flag are the same, otherwise"
means when when either of them change from the previous row, then we need to know about it.
When we see "previous row", we think the "lag" function. So here's our first cut:
SQL> select r_rate_id,r_date,r_rate,r_stop_sale,
2 lag(r_rate) over ( partition by r_rate_id order by r_date ) as prev_rate,
3 lag(r_stop_sale) over ( partition by r_rate_id order by r_date ) as prev_stop_sale
4 from t_rates;
R_RATE_ID R_DATE R_RATE R PREV_RATE P
---------- --------- ---------- - ---------- -
1 01-SEP-15 100 N
1 02-SEP-15 100 N 100 N
1 03-SEP-15 100 N 100 N
1 04-SEP-15 100 N 100 N
1 05-SEP-15 100 N 100 N
1 06-SEP-15 90 N 100 N
1 07-SEP-15 90 N 90 N
1 08-SEP-15 90 N 90 N
1 09-SEP-15 100 N 90 N
1 10-SEP-15 100 N 100 N
1 11-SEP-15 100 N 100 N
1 12-SEP-15 100 N 100 N
1 13-SEP-15 100 N 100 N
1 14-SEP-15 100 N 100 N
1 15-SEP-15 100 Y 100 N
1 16-SEP-15 100 Y 100 Y
1 17-SEP-15 100 Y 100 Y
1 18-SEP-15 100 Y 100 Y
1 19-SEP-15 100 N 100 Y
1 20-SEP-15 100 N 100 N
Now we want to check when there is a *change* between the current and the lag, and the first row is a special case, because we've "commenced". So here is our second cut
SQL> select
2 r_rate_id,r_date,r_rate,r_stop_sale,
3 case
4 when prev_rate is null
5 or prev_rate != r_rate
6 or prev_stop_sale != r_stop_sale
7 then 'NEW_GROUP'
8 end seq
9 from (
10 select r_rate_id,r_date,r_rate,r_stop_sale,
11 lag(r_rate) over ( partition by r_rate_id order by r_date ) as prev_rate,
12 lag(r_stop_sale) over ( partition by r_rate_id order by r_date ) as prev_stop_sale
13 from t_rates
14 )
15 /
R_RATE_ID R_DATE R_RATE R SEQ
---------- --------- ---------- - ---------
1 01-SEP-15 100 N NEW_GROUP
1 02-SEP-15 100 N
1 03-SEP-15 100 N
1 04-SEP-15 100 N
1 05-SEP-15 100 N
1 06-SEP-15 90 N NEW_GROUP
1 07-SEP-15 90 N
1 08-SEP-15 90 N
1 09-SEP-15 100 N NEW_GROUP
1 10-SEP-15 100 N
1 11-SEP-15 100 N
1 12-SEP-15 100 N
1 13-SEP-15 100 N
1 14-SEP-15 100 N
1 15-SEP-15 100 Y NEW_GROUP
1 16-SEP-15 100 Y
1 17-SEP-15 100 Y
1 18-SEP-15 100 Y
1 19-SEP-15 100 N NEW_GROUP
1 20-SEP-15 100 N
Now we are getting places. Of course the string "new group" isnt much use, because we would like some way of 'numbering' each new group so we can use it later in a 'group by'. So we recast it slightly
SQL> select
2 r_rate_id,r_date,r_rate,r_stop_sale,
3 case
4 when prev_rate is null
5 or prev_rate != r_rate
6 or prev_stop_sale != r_stop_sale
7 then row_number() over (partition by r_rate_id order by r_date)
8 end seq
9 from (
10 select r_rate_id,r_date,r_rate,r_stop_sale,
11 lag(r_rate) over ( partition by r_rate_id order by r_date ) as prev_rate,
12 lag(r_stop_sale) over ( partition by r_rate_id order by r_date ) as prev_stop_sale
13 from t_rates
14 )
15 /
R_RATE_ID R_DATE R_RATE R SEQ
---------- --------- ---------- - ----------
1 01-SEP-15 100 N 1
1 02-SEP-15 100 N
1 03-SEP-15 100 N
1 04-SEP-15 100 N
1 05-SEP-15 100 N
1 06-SEP-15 90 N 6
1 07-SEP-15 90 N
1 08-SEP-15 90 N
1 09-SEP-15 100 N 9
1 10-SEP-15 100 N
1 11-SEP-15 100 N
1 12-SEP-15 100 N
1 13-SEP-15 100 N
1 14-SEP-15 100 N
1 15-SEP-15 100 Y 15
1 16-SEP-15 100 Y
1 17-SEP-15 100 Y
1 18-SEP-15 100 Y
1 19-SEP-15 100 N 19
1 20-SEP-15 100 N
So if we could just have those sequence numbers flow down and fill in the gaps, we'd be pretty close ... So I'll call the SQL above "baseline" and we'll wrap that with a window analytic to fill in the gaps
SQL> with baseline as (
2 select
3 r_rate_id,r_date,r_rate,r_stop_sale,
4 case
5 when prev_rate is null
6 or prev_rate != r_rate
7 or prev_stop_sale != r_stop_sale
8 then row_number() over (partition by r_rate_id order by r_date)
9 end seq
10 from (
11 select r_rate_id,r_date,r_rate,r_stop_sale,
12 lag(r_rate) over ( partition by r_rate_id order by r_date ) as prev_rate,
13 lag(r_stop_sale) over ( partition by r_rate_id order by r_date ) as prev_stop_sale
14 from t_rates
15 )
16 )
17 select
18 r_rate_id,r_date,r_rate,r_stop_sale,
19 max(seq) over ( partition by r_rate_id order by r_date) as flow_down
20 from baseline
21 /
R_RATE_ID R_DATE R_RATE R FLOW_DOWN
---------- --------- ---------- - ----------
1 01-SEP-15 100 N 1
1 02-SEP-15 100 N 1
1 03-SEP-15 100 N 1
1 04-SEP-15 100 N 1
1 05-SEP-15 100 N 1
1 06-SEP-15 90 N 6
1 07-SEP-15 90 N 6
1 08-SEP-15 90 N 6
1 09-SEP-15 100 N 9
1 10-SEP-15 100 N 9
1 11-SEP-15 100 N 9
1 12-SEP-15 100 N 9
1 13-SEP-15 100 N 9
1 14-SEP-15 100 N 9
1 15-SEP-15 100 Y 15
1 16-SEP-15 100 Y 15
1 17-SEP-15 100 Y 15
1 18-SEP-15 100 Y 15
1 19-SEP-15 100 N 19
1 20-SEP-15 100 N 19
And now we're just about done...I can use my "flow_down" column just like a normal group by, because I just need the max/min R_DATE
SQL> with baseline as (
2 select
3 r_rate_id,r_date,r_rate,r_stop_sale,
4 case
5 when prev_rate is null
6 or prev_rate != r_rate
7 or prev_stop_sale != r_stop_sale
8 then row_number() over (partition by r_rate_id order by r_date)
9 end seq
10 from (
11 select r_rate_id,r_date,r_rate,r_stop_sale,
12 lag(r_rate) over ( partition by r_rate_id order by r_date ) as prev_rate,
13 lag(r_stop_sale) over ( partition by r_rate_id order by r_date ) as prev_stop_sale
14 from t_rates
15 )
16 )
17 select r_rate_id,min(r_date),max(r_date), r_rate,r_stop_sale
18 from
19 (
20 select
21 r_rate_id,r_date,r_rate,r_stop_sale,
22 max(seq) over ( partition by r_rate_id order by r_date) as flow_down
23 from baseline
24 )
25 group by r_rate_id,r_rate,r_stop_sale, flow_down
26 order by flow_down
27 /
R_RATE_ID MIN(R_DAT MAX(R_DAT R_RATE R
---------- --------- --------- ---------- -
1 01-SEP-15 05-SEP-15 100 N
1 06-SEP-15 08-SEP-15 90 N
1 09-SEP-15 14-SEP-15 100 N
1 15-SEP-15 18-SEP-15 100 Y
1 19-SEP-15 20-SEP-15 100 N
There are actually more concise way of doing the same job, but this way lets me build it up and explain as we go.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment