Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Rows should return closest of less than the given date

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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' ..."

    ... 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
    ;
    Last edited by tonkuma; 09-27-11 at 10:15. Reason: Add second inconsistency

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Thank you very much..IT IS WORKING FINE

    Thanks a lot..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •