LAST_VALUE and 1 FOLLOWING AND 1 FOLLOWING

Analytics
Enthusiast

LAST_VALUE and 1 FOLLOWING AND 1 FOLLOWING

I need help with the LAST_VALUE using a partition. I am trying to get a disposition date for each row, where the disposition date is the following end date (by COL1 and COL3). If there is not a following end date then NULL. My issue is the results are returning extra rows where the end date and disposition date are the same. What am I missing? Thank you!

 

Query:

SELECT COL1, COL2, COL3, END_DT, 
LAST_VALUE(END_DT) OVER(PARTITION BY COL1, COL3 ORDER BY COL3, END_DT ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) DISP_DT

 

Original Data:

COL1COL2COL3END_DT
123428930class13/8/2013
123428930class11/26/2015
123428930class14/5/2016
123428930class21/26/2015

 

Results (rows with strikethrough I don't want to see):

COL1COL2COL3END_DTDISP_DT
123428930class13/8/20133/8/2013
123428930class13/8/20131/26/2015
123428930class11/26/20151/26/2015
123428930class11/26/20154/5/2016
123428930class14/5/20164/5/2016
123428930class14/5/2016 
123428930class21/26/20151/26/2015
123428930class21/26/2015 
2 REPLIES
Junior Contributor

Re: LAST_VALUE and 1 FOLLOWING AND 1 FOLLOWING

This is not an issue of LAST_VALUE, it's due to the base query, probably a bad join.

Enthusiast

Re: LAST_VALUE and 1 FOLLOWING AND 1 FOLLOWING

Thanks, you were right...bad join. All is good now!