Hello
I have a data set of subscriptions where I can have multiple subscriptions for the exact same date range, but I need to find the end date of the previous set of subscriptions.
If I use LAG it will only look at the previous row
with src as (
select 1 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 2 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 3 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 4 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 5 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 6 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 7 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 8 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 9 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 10 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 11 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 12 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual
)
SELECT
id,
start_date,
end_date,
lag(end_date) over(order by end_date,start_date) prev_end_dt, --date I get
date_i_want
FROM
src
/
and so I'm trying to come up with a simple and robust way to find that previous end date. The simplest way I can find is to use the following:
with src as (
select 1 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 2 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 3 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 4 id, to_date('01/01/2016','dd/mm/yyyy') start_date, to_date('13/12/2016','dd/mm/yyyy') end_date, cast(null as date) date_I_want from dual union all
select 5 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 6 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 7 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 8 id, to_date('01/01/2017','dd/mm/yyyy') start_date, to_date('13/12/2017','dd/mm/yyyy') end_date, to_date('13/12/2016','dd/mm/yyyy') date_I_want from dual union all
select 9 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 10 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 11 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual union all
select 12 id, to_date('01/01/2018','dd/mm/yyyy') start_date, to_date('13/12/2018','dd/mm/yyyy') end_date, to_date('13/12/2017','dd/mm/yyyy') date_I_want from dual
)
SELECT
id,
start_date,
end_date,
MAX( CASE
WHEN rn=1 THEN null
WHEN
end_date <> NVL(prev_end_date,end_date)
THEN
prev_end_date
END
) over(order by start_date,end_date),
date_i_want
FROM
(
SELECT
id,
start_date,
end_date,
row_number() over( order by end_date,start_date) rn,
lag(end_date) over(order by end_date,start_date) prev_end_date,
date_i_want
FROM
src
)
/
But I'm not sure if there's a better option?