Rows should return closest of less than the given date

Billa007 · #1 (**permalink**) 09-27-11, 06:31

Hi
Please find the query

Code:

EXEC SQL                                        
  DECLARE res_CS CURSOR WITH RETURN FOR


SELECT                     
T1.CD_PLT
,T1.PRE_NAME
,T1.BASE_NAME
,T1.SUF_NAME
,S1.DEAL_CODE
,T1.MAX_WX
,T2.WKLY_CA
,T2.IN_DT
,T2.MAX_IN_DT
,T3.MISC_TXT
FROM TABLE1 T1

  INNER JOIN                                       
     SUB_TABLE S1                            
ON                                                 
    T1.ID_NO        = S1.ID_NO          
AND T1.CD_PLT     = S1.CD_PLT       
AND T1.BASE_NAME    = S1.BASE_NAME      
AND T1.PRE_NAME  = S1.PRE_NAME    
AND T1.SUF_NAME = S1.SUF_NAME     

                                                   
LEFT OUTER JOIN                                    
      TABLE2  T2 
ON                      
  AND T2.CD_PLT     = T1.CD_PLT       
AND T2.BASE_NAME    = T1.BASE_NAME      
AND T2.PRE_NAME  = T1.PRE_NAME    
AND T2.SUF_NAME = T1.SUF_NAME     
 
                                                    
 LEFT OUTER JOIN                                    
       TABLE3 T3                          
   ON                                                 
    T3.ID_NO        = T1.ID_NO          
AND T3.CD_PLT     = T1.CD_PLT       
AND T3.BASE_NAME    = T1.BASE_NAME      
AND T3.PRE_NAME  = T1.PRE_NAME    
AND T3.SUF_NAME = T1.SUF_NAME     
   
                                                    
 WHERE

                                                   
     T1.ID_NO      = 1111                       
 AND T1.BASE_NAME   >= :WS-BASE'          
 AND T1.PRE_NAME>= :WS-PRE
AND T1.SUF_NAME >= :WS-SUF

Table details

Code:

Table1


CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME    MAX_WK   
----------- ------------ -------------- ---------- -----------
ABACC            6065        REWS           AE     20
ABACC            6065        REWS           AE     20
ABACC            6000        ERWV           DE     45
BDDER            R443        THYR           RE     56
BDDER            4565        RTY            RT     20
E1111            1111        TRW            HJ     56 
FRGET            ADER        TRR            FR     20
FRGET            ADER        TRR            FR     20
FRGET            7777        GHYU           FF     66
HYYYY            8888        ADDD           WE     56
HYYYY            9999        ADDD           RE     20
SSSSS            5666        TTYG           YU     76


SUB_TABLE


CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME    DEAL_CODE      
----------- ------------ -------------- ---------- ---------------- 
ABACC            6065        REWS           AE        BYT2
ABACC            6065        REWS           AE        BZT3
ABACC            6000        ERWV           DE        CRER
BDDER            R443        THYR           RE        THRR
BDDER            4565        RTY            RT        TEEE
E1111            1111        TRW            HJ        T56T
FRGET            ADER        TRR            FR        EWWE
FRGET            ADER        TRR            FR        ERR2
FRGET            7777        GHYU           FF        TR66
HYYYY            8888        ADDD           WE        TW56
HYYYY            9999        ADDD           RE        MG20
SSSSS            5666        TTYG           YU        UYYT 

TABLE2

CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA      IN_DT           MAX_IN_DT          
----------- ------------ -------------- ---------- -------    ---------------- ------------
ABACC            6065        REWS           AE     21          2009-01-13       2011-12-19
ABACC            6065        REWS           AE     21          2009-01-12       2011-12-19
ABACC            6000        ERWV           DE     46          2010-11-11       2011-12-19
BDDER            R443        THYR           RE     57          2010-12-22       2011-12-19 
BDDER            4565        RTY            RT     27          2011-01-16       2011-12-19
FRGET            ADER        TRR            FR     26          2011-08-13       2011-12-19
FRGET            ADER        TRR            FR     25          2011-09-19       2011-12-19
FRGET            7777        GHYU           FF     61          2012-10-13       2013-12-19
HYYYY            8888        ADDD           WE     57          2010-06-10       2011-12-19
HYYYY            9999        ADDD           RE     21          2010-07-12       2011-12-19 
SSSSS            5666        TTYG           YU     66          2009-01-12       2011-12-19
SSSSS            5666        TTYG           YU     66          2010-03-15       2011-12-19
ZZZZZ            5434        TREF           YH     55          2010-05-11       2011-12-19

TABLE3

CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  MISC_TXT 
----------- ------------ -------------- ---------- -------  
ABACC            6065        REWS           AE        TEST
ABACC            6065        REWS           AE        TEST
ABACC            6000        ERWV           DE        TEST
BDDER            R443        THYR           RE        TEST
BDDER            4565        RTY            RT        TEST
E1111            1111        TRW            HJ        TEST 
FRGET            ADER        TRR            FR        TEST
FRGET            ADER        TRR            FR        TEST
FRGET            7777        GHYU           FF        TEST
HYYYY            8888        ADDD           WE        TEST
HYYYY            9999        ADDD           RE        TEST
SSSSS            5666        TTYG           YU        TEST

Getting output

Code:

OUTPUT


CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  DEAL_CODE  MAX_WK      WKLY_CA      IN_DT           MAX_IN_DT   MISC_TXT
----------- ------------ -------------- ---------- --------- -------------------------------------- ------------   -------
ABACC            6065        REWS           AE     BYT2		20       21          2009-01-13       2011-12-19  TEST
ABACC            6065        REWS           AE     BZT3 	20       21          2009-01-12       2011-12-19  TEST
ABACC            6000        ERWV           DE     CRER 	45       46          2010-11-11       2011-12-19  TEST
BDDER            R443        THYR           RE     THRR 	56       57          2010-12-22       2011-12-19  TEST
BDDER            4565        RTY            RT     TEEE 	20       27          2011-01-16       2011-12-19  TEST
FRGET            ADER        TRR            FR     EWWE 	20       26          2011-08-13       2011-12-19  TEST
FRGET            ADER        TRR            FR     ERR2 	20       25          2011-09-19       2011-12-19  TEST
FRGET            7777        GHYU           FF     TR66 	66       61          2012-10-13       2013-12-19  TEST
HYYYY            8888        ADDD           WE     TW56 	56       57          2010-06-10       2011-12-19  TEST
HYYYY            9999        ADDD           RE     MG20 	20       21          2010-07-12       2011-12-19  TEST 
SSSSS            5666        TTYG           YU     UYYT         76       66          2009-01-12       2011-12-19  TEST
SSSSS            5666        TTYG           YU     UYYT         -        66          2010-03-15       2011-12-19  TEST

Output should be

Code:

CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  DEAL_CODE  MAX_WK      WKLY_CA      IN_DT           MAX_IN_DT   MISC_TXT
----------- ------------ -------------- ---------- --------- -------------------------------------- ------------   -------
ABACC            6065        REWS           AE     BYT2		20       21          2009-01-13       2011-12-19  TEST
ABACC            6000        ERWV           DE     CRER 	45       46          2010-11-11       2011-12-19  TEST
BDDER            R443        THYR           RE     THRR 	56       57          2010-12-22       2011-12-19  TEST
BDDER            4565        RTY            RT     TEEE 	20       27          2011-01-16       2011-12-19  TEST
FRGET            ADER        TRR            FR     ERR2 	20       25          2011-09-19       2011-12-19  TEST
FRGET            7777        GHYU           FF     TR66 	66       61          2012-10-13       2013-12-19  TEST
HYYYY            8888        ADDD           WE     TW56 	56       57          2010-06-10       2011-12-19  TEST
HYYYY            9999        ADDD           RE     MG20 	20       21          2010-07-12       2011-12-19  TEST 
SSSSS            5666        TTYG           YU     UYYT         -        66          2010-03-15       2011-12-19  TEST

My query should have the records like below

condition is : select the all the rows when the In_DT <= '2011-12-31' and select the closest In_DT to the '2011-12-31'value rows should be displayed

ex

ABACC 6065 REWS AE BYT2 20 21 2009-01-13 2011-12-19 TEST
ABACC 6065 REWS AE BZT3 20 21 2009-01-12 2011-12-19 TEST

shuold return one row for the above scenerio

ABACC 6065 REWS AE BYT2 20 21 2009-01-13 2011-12-19 TEST

Above row is the closest date(IN_DT) to '2011-12-31'
Please help me,how to change the query

tonkuma · #2 (**permalink**) 09-27-11, 09:09

There are (at least) two inconsistencies in your example
1) second column and third column are changed in "SELECT list" and "Getting output".
2) You included a rwo in_dt = '2012-10-13' in your Output.
But, you wrote "condition is : select the all the rows when the In_DT <= '2011-12-31' ..."

Quote:

... select the closest In_DT to the '2011-12-31' ...

"the closest in which group" is not clear.

Anyhow, here is an example:

Code:

SELECT CD_PLT
     , PRE_NAME
       ...
     , MISC_TXT
 FROM  (/* put your query here with an additional ROW_NUMBER() column,
             like the following...
        */
        SELECT T1.CD_PLT
             , T1.PRE_NAME
               ...
             , T3.MISC_TXT
             , ROW_NUMBER()
                  OVER(PARTITION BY T1.CD_PLT, T1.BASE_NAME
                           ORDER BY T1.in_dt DESC) AS rn
         FROM  TABLE1 T1
               ...
               ...
       ) s  
 WHERE rn = 1
;

Billa007 · #3 (**permalink**) 09-27-11, 09:47

Hi;

Thank you very much..IT IS WORKING FINE

Thanks a lot..

Internet Services

Web Development

Digital Marketing

Consumer Tech