Dynamic Selection based on Date Comparision

Database

Dynamic Selection based on Date Comparision

Hallo All

I have a requirement that requires to join 2 tables and then select a key value for the record that doesn't have one.

To be clear - pls go through the example provided below,

Here in MASTER contains KEY assigned to particular date ranges, the MISSING table contains a particular date range for which the key is not assigned







MASTER        
KEY START DT END DT GROUP DEPT MGR ID
1 1/1/2010 12/31/2010 A1 11 01
2 6/30/2011 12/31/2011 A1 11 01
3 1/1/2012 12/31/9999 A1 11 01
           
MISSING        
KEY START DT END DT GROUP DEPT MGR ID
? 1/1/2011 5/31/2011 A1 11 01

The output should be as below - where we join both table on columns 4,5,6 and then assign the KEY value which is greater nearest to the







OUTPUT        
KEY START DT END DT GROUP DEPT MGR ID
1 1/1/2010 12/31/2010 A1 11 01
2 1/1/2011 5/31/2011 A1 11 01
2 6/30/2011 12/31/2011 A1 11 01
3 1/1/2012 12/31/9999 A1 11 01

This I have it done by the below query,

SEL 
GRP, DEPT, MGR_ID,
ROW_NUMBER() OVER (PARTITION BY GRP, DEPT, MGR_ID ORDER BY START_DT ) AS ROWNUM
FROM
MASTER A
JOIN
MISSING B
ON
A.GRP=B.GRP AND A.DEPT=B.DEPT AND A.MGR_ID=B.MGR_ID AND
A.START_DT < = B.START_DT
WHERE ROWNUM=1

But when the MISSING date range is outside i.e greater  than the max start_dt or lesser than the min start_dt in both the scenario I am not able to assign the key value,







MASTER        
KEY START DT END DT GROUP DEPT MGR ID
1 1/1/2010 12/31/2010 A1 11 01
2 1/1/2011 12/31/2011 A1 11 01
3 1/1/2012 6/30/2012 A1 11 01
           
MISSING        
KEY START DT END DT GROUP DEPT MGR ID
? 7/1/2012 12/31/2012 A1 11 01
           
OUTPUT        
KEY START DT END DT GROUP DEPT MGR ID
1 1/1/2010 12/31/2010 A1 11 01
2 1/1/2011 12/31/2011 A1 11 01
3 1/1/2012 6/30/2012 A1 11 01
3 7/1/2012 12/31/2012 A1 11 01

Request the guru's for guidance in acheiving this.