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
Enthusiast

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

May be in this way

`select case when timeperiod < maturity                      then timeperiod             else timeperiodqualify 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 xFROM tabQUALIFY 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>TIMEPDUNION ALLSEL 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`