Find the two pre/post/both the Dates

Database
Enthusiast

Find the two pre/post/both the Dates

Hi everyone, I need some help determining the logic for the below scenario,

maturity         timeperiod

6/13/2010      8/13/2007

6/13/2010      7/25/2009

6/13/2010      3/12/2010

6/13/2010      5/23/2010

11/23/2012    3/23/2011

11/23/2012    12/11/2011

11/23/2012    11/28/2012

11/23/2012    12/20/2012

11/23/2012    2/12/2013

2/14/2013      3/16/2013

2/14/2013      4/13/2013

2/14/2013      6/14/2014

2/14/2013      7/23/2016

Based on maturity field,

i need to find the two previous dates in timeperiod,

if there is no previous i need to find two post dates in timeperiod,

if both previous and post date present in that group means i need to find 1 previous and 1 post date in timeperiod field.

my final result would be like this

maturity            timeperiod

6/13/2010      3/12/2010

6/13/2010      5/23/2010


11/23/2012    12/11/2011

11/23/2012    11/28/2012


2/14/2013      3/16/2013

2/14/2013      4/13/2013

Tags (1)
3 REPLIES
DS
Enthusiast

Re: Find the two pre/post/both the Dates

May be in this way

select case when timeperiod < maturity
then timeperiod
else timeperiod
qualify rank() over (order by maturity, timeperiod desc)<=2

Senior Apprentice

Re: Find the two pre/post/both the Dates

Nice puzzle :-)

This should work, there are probably other solutions, but this one needs only a single STAT fubnction step:

SELECT
maturity,
timeperiod,
COUNT(CASE WHEN timeperiod <= maturity THEN 1 END) OVER (PARTITION BY maturity) AS cnt1,
COUNT(CASE WHEN timeperiod > maturity THEN 1 END) OVER (PARTITION BY maturity) AS cnt2,
ROW_NUMBER() OVER (PARTITION BY maturity ORDER BY timeperiod) AS rn,

rn - CASE
WHEN cnt1 > 0 AND cnt2 > 0 THEN cnt1 -1
WHEN cnt1 > 0 THEN cnt1-2
ELSE cnt1
END AS x
FROM tab
QUALIFY x IN (1,2)

Dieter

Enthusiast

Re: Find the two pre/post/both the Dates

Thanks Dieter it is working fine.. below is the query i had done...

SEL *  FROM
(SEL MATURITY,TIMEPD, ROW_NUMBER() OVER(PARTITION BY MATURITY ORDER BY TIMEPD DESC) RN FROM DTFD WHERE MATURITY>TIMEPD
UNION ALL
SEL MATURITY,TIMEPD, ROW_NUMBER() OVER(PARTITION BY MATURITY ORDER BY TIMEPD ) FROM DTFD
WHERE MATURITY<TIMEPD)T
QUALIFY ROW_NUMBER() OVER(PARTITION BY MATURITY ORDER BY MATURITY,RN)<=2