SQL - Searching for a dynamic minimum date of a partition over a time interval

user_3599514 0
Viewed 213

I am currently stuck on this SQL problem where I am trying to generate the 'Starting date' column using SQL. I was given the 'ID', 'Class', and 'Date' columns in the table below.

To generate the 'Starting date' column, I would need to get the minimum date over the partition of 'ID' and 'Class'. In addition, I would need to check if the date is less than 110 days from the minimum date. If this 110 day criteria is satisfied, then I can use the minimum date to calculate the last column ('Days difference to starting date'). Otherwise, I would need to use the date of that row as the starting date, and subsequent rows would also be using the new starting date until the 110 day criteria is not satisfied. I have highlighted the minimum date to use based on the 110 day criteria.

I started off with the following case statement, but stuck on how to complete it.

CASE
    WHEN Date <= date(min(Date) OVER (PARTITION BY ID, Class) + interval '110 day') THEN Date
    ELSE --This is where I am stuck
END as 'Starting date'

enter image description here

netezza sql
1 Answers

I would consider this a sessionize problem.

The one sticking point I see is the 2019-09-06 to the 2019-05-20. That is 109 days, which would fall into your 110 day criteria.

If you are looking to make static 110 days intervals based on a minimum date, you should be able to use a date dimension table to establish your start and stops of min dates that fit your 110 day restriction.

I put together a SQL only solution below for what I thought you might be looking for. You could perform another pass on this and daisy chain these sessions together to create a longer than 110 day session if there are events that connect them together.

Hope this helps!

select
event_data.id,
event_data.class,
event_data.date_dt,
min(event_data_2.date_dt) as starting_dt,
event_data.date_dt - min(event_data_2.date_dt) as days_between

from
(
select
'1234' as id,
'XYZ' as class,
'2019-02-04'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-05-20'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-06'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-11'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-03-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-05-05'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-10-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
) event_data
inner join
(
select
'1234' as id,
'XYZ' as class,
'2019-02-04'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-05-20'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-06'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-11'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-09-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-12'::date as date_dt
union all
select
'1234' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-03-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-05-05'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-10-02'::date as date_dt
union all
select
'4567' as id,
'XYZ' as class,
'2019-11-20'::date as date_dt
) event_data_2 on (event_data.id = event_data_2.id and event_data.class = event_data_2.class and ((event_data.date_dt - event_data_2.date_dt) <= 110))
group by
event_data.id,
event_data.class,
event_data.date_dt
user_2149480
Related