Ordered Analytic function with case statement

Analytics
Enthusiast

Ordered Analytic function with case statement

I need to rollup a set of records where the current row date and following date are less than 60 days apart. But I have an instance where there are multiple records for the same key that are less than 60 days.

 

Example Data:

COMP_KEYCOMP_END_DTDAYS 
18,4183/7/2014 
13,2097/2/2014 
18,4077/24/2014167
18,4071/7/2015

54

18,4073/2/201514
18,4073/16/201512
18,4073/28/201517
18,4074/14/201521
18,4075/5/2015 
19,8065/14/2015 
18,4078/6/2015 

 

Comp_key 18,407 has 5 instances where the days between are less than 60. I would like my results to be the following.

 

Expected Results:

COMP_KEYCOMP_END_DT
18,4183/7/2014
13,2097/2/2014
18,4077/24/2014
18,4075/5/2015
19,8065/14/2015
18,4078/6/2015

 

This it what I have so far for the query, but I can't figure out how to set it up so it selects the MAX comp_end_dt from the partition. I have tried UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING but still couldn't get it to work. 

 

SELECT DISTINCT cnst_mstr_id, person_key, comp_key, comp_end_dt,
(CASE WHEN LAST_VALUE(comp_end_dt) OVER(PARTITION BY cnst_mstr_id, person_key, comp_key ORDER BY comp_end_dt ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)-comp_end_dt <= 60 THEN
LAST_VALUE(comp_end_dt) OVER(PARTITION BY cnst_mstr_id, person_key, comp_key ORDER BY comp_end_dt ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) ELSE comp_end_dt END) comp_end_dt
FROM comm_mstr_tmp1

 

Results from existing query:

COMP_KEYCOMP_END_DTLAST_VALUE
18,4183/7/20143/7/2014
13,2097/2/20147/2/2014
18,4077/24/20147/24/2014
18,4071/7/20153/2/2015
18,4073/2/20153/16/2015
18,4073/16/20153/28/2015
18,4073/28/20154/14/2015
18,4074/14/20155/5/2015
18,4075/5/20155/5/2015
19,8065/14/20155/14/2015
18,4078/6/20158/6/2015

 

I am new to these functions, so any help would be greatly appreciated.


Accepted Solutions
Senior Apprentice

Re: Ordered Analytic function with case statement

This should return the expected result:

SELECT cnst_mstr_id, person_key, comp_key, comp_end_dt,
   Last_Value(comp_end_dt)
   Over(PARTITION BY cnst_mstr_id, person_key, comp_key
        ORDER BY comp_end_dt ASC
        ROWS BETWEEN 1 Following AND 1 Following)-comp_end_dt AS days_between
FROM comm_mstr_tmp1
QUALIFY
   days_between > 60    -- more than 60 days
OR days_between IS NULL -- or last row
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: Ordered Analytic function with case statement

This should return the expected result:

SELECT cnst_mstr_id, person_key, comp_key, comp_end_dt,
   Last_Value(comp_end_dt)
   Over(PARTITION BY cnst_mstr_id, person_key, comp_key
        ORDER BY comp_end_dt ASC
        ROWS BETWEEN 1 Following AND 1 Following)-comp_end_dt AS days_between
FROM comm_mstr_tmp1
QUALIFY
   days_between > 60    -- more than 60 days
OR days_between IS NULL -- or last row
Enthusiast

Re: Ordered Analytic function with case statement

Thank you so much!! It works beautifully. Smiley Happy