Skip to Main Content
  • Questions

  • Date range grouping for same price and stop sale option

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stephan.

Asked: September 24, 2015 - 3:07 pm UTC

Last updated: October 30, 2016 - 9:52 am UTC

Version: 10G R2

Viewed 1000+ times

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

Comments

With more recent versions of Oracle...

Stew Ashton, September 25, 2015 - 9:32 am UTC

The questioner is using Oracle 10gR2, which is getting pretty old. When he moves to 12c, he can do the same job more efficiently using the MATCH_RECOGNIZE clause:
select * from t_rates
match_recognize(
  partition by r_rate_id order by r_date
  measures first(r_date) first_date, last(r_date) last_date,
    r_rate r_rate, r_stop_sale r_stop_sale
  pattern(a b*)
  define b as (r_rate, r_stop_sale) = ((prev(r_rate), prev(r_stop_sale)))
);
The MATCH_RECOGNIZE clause is often very handy when dates or date ranges are involved.
Connor McDonald
September 25, 2015 - 11:32 am UTC

"The questioner is using Oracle 10gR2, which is getting pretty old"

So am I ... so am I :-)

Thanks for your input Stew, always appreciated.

Asked and Answered by Tomkyte - Oracle magazine

Rajeshwaran Jeyabal, September 25, 2015 - 3:33 pm UTC

Asked and Answered by Tomkyte - Oracle magazine ( look for Analytics on rescue )

http://www.oracle.com/technetwork/issue-archive/o24asktom-095715.html 

rajesh@ORA11G> select r_rate_id, min(r_date), max(r_date), r_rate
  2  from (
  3  select r_rate_id, r_date, r_rate, r_stop_sale,
  4    last_value(rn ignore nulls) over(order by r_date) rn
  5  from (
  6  select t.* ,
  7    case when lag(r_rate) over(order by r_date) is null
  8      or lag(r_stop_sale) over(order by r_date) is null
  9      or r_rate <> lag(r_rate) over(order by r_date)
 10      or r_stop_sale <> lag(r_stop_sale) over(order by r_date)
 11    then row_number() over( order by r_date ) end rn
 12  from t
 13       )
 14       )
 15  group by r_rate_id, r_rate, rn
 16  order by r_rate_id, 2
 17  /

 R_RATE_ID MIN(R_DATE)             MAX(R_DATE)                 R_RATE
---------- ----------------------- ----------------------- ----------
         1 01-SEP-2015 12:00:00 AM 05-SEP-2015 12:00:00 AM        100
         1 06-SEP-2015 12:00:00 AM 08-SEP-2015 12:00:00 AM         90
         1 09-SEP-2015 12:00:00 AM 14-SEP-2015 12:00:00 AM        100
         1 15-SEP-2015 12:00:00 AM 18-SEP-2015 12:00:00 AM        100
         1 19-SEP-2015 12:00:00 AM 20-SEP-2015 12:00:00 AM        100

5 rows selected.

rajesh@ORA11G>


Works like a charm....

Stephan K, October 06, 2015 - 2:01 pm UTC

The "build-up" explanation definitely helped me solve some additional challenges I came across.

Thank you!

You saved my derrier, yet again

Pedro, October 29, 2016 - 3:19 pm UTC

Where do true men go in times of complete loss of confidence in their analytical functions abilities? asktom.oracle.com! I swear, I spent 2 hours trying to figure this "next value"/lag business out, last little bit to finish a big project using analytical functions. Many thanks.
Connor McDonald
October 30, 2016 - 9:52 am UTC

:-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.