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

    Unanswered: query returns latest date row

    Hi;


    The below query gives the latest date row..My requirement is like below
    Code:
    SELECT CD_PLT
         , PRE_NAME
         ,IN_DT
         ,OUT_DT_FLAG
      
     FROM  (
            SELECT T1.CD_PLT
                 , T1.PRE_NAME
                 , T2.IN_DT
                 ,case when(T2.IN_DT > '2012-12-31')
                   then 'y'
                   else 'N'
                   end OUT_DT_FLAG 
                  
            
                 , ROW_NUMBER()
                      OVER(PARTITION BY T1.CD_PLT, T1.PRE_NAME
                               ORDER BY T2.IN_DT DESC) AS rn
             FROM  TABLE1 T1
      LEFT OUTER JOIN
                   TABLE2 T2
    ON
        T1.CD_PLT=T2.CD_PLT
        T1.PRE_NAME=T2.PRE_NAME  
    
                 
           ) s  
     WHERE rn = 1
    ;
    The below query gives the latest date row..My requirement is like below

    If the T2.IN_DT has two records like less than and greater than of 2012-12-31 means,
    we have to display the less than IN_DT record, at the same time the OUT_DT_FLAG will be setting
    the value 'Y' else 'N'

    If the T2.IN_DT has only greater date means,the T2.IN_DT will be setting empty and the OUT_DT_FLAG will be setting 'Y'

    Input Table

    Code:
    CD_PLT         PRE_NAME      IN_DT             
    ----------- ------------ --------------  
    ABACC            6065        2013-01-13    
    ABACC            6065        2011-01-11     
    ABACC            6077        2011-01-12 
    ABACC            6077        2012-01-10    
    ABACC            6000        2011-10-09  
    ABACC            6000        2010-11-10 
    ABACC            6000        2009-11-11     
    BBBBB            7777        2015-02-22
    BBBBB            7777        2014-01-21
    MY QUERY RETURNS LIKE BELOW

    Code:
    CD_PLT         PRE_NAME      IN_DT       OUT_DT_FLAG          
    ----------- ------------ --------------  --------------- 
    ABACC            6065        2013-01-13     Y 
    ABACC            6077        2012-01-12     Y
    ABACC            6000        2011-10-09     N
    BBBBB            7777        2015-02-22     Y
    EXPECTED RESULT

    Code:
    CD_PLT         PRE_NAME      IN_DT       OUT_DT_FLAG          
    ----------- ------------ --------------  --------------- 
    ABACC            6065        2011-01-11     Y 
    ABACC            6077        2011-01-12     Y
    ABACC            6000        2011-10-09     N
    BBBBB            7777                       Y
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please write basic information by referencing http://www.dbforums.com/db2/854783-m...e-posting.html.
    For example:
    1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.

    I thought that your descriptions were incomplete and inconsistent.

    For example:
    1) Although there were TABLE1 and TABLE2 in your code,
    you showed sample data of "Input Table".

    2)
    If the T2.IN_DT has two records like less than and greater than of 2012-12-31 means,
    we have to display the less than IN_DT record, ...

    If the T2.IN_DT has only greater date means,the T2.IN_DT will be setting empty ...
    These two rows are both less than 2012-12-31, but you showed only 2011-01-12 in EXPECTED RESULT.
    Code:
    ABACC            6077        2011-01-12 
    ABACC            6077        2012-01-10
    No description for the condition two(or more) rows are both less than 2012-12-31.
    No description the reason to exclude 2012-01-10.

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

    Db2Version is
    DB2 SQL PRECOMPILER VERSION 9 REL. 1.0

    1.Input Table..I was just taking the sample records of after JOINS

    2.Apology for that...I could have missed to type the reason

    Code:
    ABACC            6077        2011-01-12 
    ABACC            6077        2012-01-10-->lessthan of 2012-12-31 & latest
    for the above two records ...Need to select the latest record and should return to the Expected Result...

    To fetch one latest IN_DT record for each CD_PLT,PRE_NAME,I was using the DESC IN_DT and ROW_NUMBER() code

    Table1
    Code:
    CD_PLT         PRE_NAME                   
    ----------- ------------   
    ABACC            6065        
    ABACC            6065         
    ABACC            6077       
    ABACC            6077          
    ABACC            6000         
    ABACC            6000       
    ABACC            6000            
    BBBBB            7777        
    BBBBB            7777
    TABLE2
    Code:
    CD_PLT         PRE_NAME      IN_DT             
    ----------- ------------ --------------  
    ABACC            6065        2013-01-13    
    ABACC            6065        2011-01-11 -->lessthan of 2012-12-31 & latest
        
    ABACC            6077        2012-01-10 -->lessthan of 2012-12-31 & latest   
    ABACC            6077        2011-01-12 
    
    ABACC            6000        2011-10-09  -->lessthan of 2012-12-31 & latest
    ABACC            6000        2010-11-10 
    ABACC            6000        2009-11-11     
    
    BBBBB            7777        2015-02-22
    BBBBB            7777        2014-01-21
    EXPECTED RESULT SET
    Code:
    CD_PLT         PRE_NAME      IN_DT       OUT_DT_FLAG          
    ----------- ------------ --------------  --------------- 
    ABACC            6065        2011-01-11     Y 
    ABACC            6077        2011-01-12     N
    ABACC            6000        2011-10-09     N
    BBBBB            7777                   Y
    Please help;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Note 1: IN_DT of second row of EXPECTED RESULT SET was 2011-01-12.
    But, comment in TABLE2 showed
    Code:
    ABACC            6077        2012-01-10 -->lessthan of 2012-12-31 & latest
    I took 2012-01-10

    Note 2: rnum is used to make order of result rows same as your EXPECTED RESULT SET.
    But, it is not guaranteed.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Table1(CD_PLT , PRE_NAME) AS (
    VALUES
      ( 'ABACC' , 6065 )
    , ( 'ABACC' , 6065 )
    , ( 'ABACC' , 6077 )
    , ( 'ABACC' , 6077 )
    , ( 'ABACC' , 6000 )
    , ( 'ABACC' , 6000 )
    , ( 'ABACC' , 6000 )
    , ( 'BBBBB' , 7777 )
    , ( 'BBBBB' , 7777 )
    )
    , Table2(CD_PLT , PRE_NAME , IN_DT) AS (
    VALUES
      ( 'ABACC' , 6065 , '2013-01-13' )
    , ( 'ABACC' , 6065 , '2011-01-11' )
    , ( 'ABACC' , 6077 , '2012-01-10' )
    , ( 'ABACC' , 6077 , '2011-01-12' )
    , ( 'ABACC' , 6000 , '2011-10-09' )
    , ( 'ABACC' , 6000 , '2010-11-10' )
    , ( 'ABACC' , 6000 , '2009-11-11' )
    , ( 'BBBBB' , 7777 , '2015-02-22' )
    , ( 'BBBBB' , 7777 , '2014-01-21' )
    )
    SELECT cd_plt
         , pre_name
         , in_dt
         , CASE
           WHEN in_dt IS NULL THEN 'Y'
           WHEN rnum2 = 1     THEN 'N'
           ELSE                    'Y'
           END  AS out_dt_flag
     FROM  (SELECT t.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY cd_plt , pre_name
                                ORDER BY in_dt DESC  NULLS LAST
                          ) rnum1
                 , ROW_NUMBER()
                      OVER( PARTITION BY cd_plt , pre_name
                                ORDER BY in_dt DESC  NULLS FIRST
                          ) rnum2
             FROM  (SELECT t1.cd_plt
                         , t1.pre_name
                         , CASE
                           WHEN t2.in_dt <= '2012-12-31' THEN
                                t2.in_dt
                           END  AS in_dt
                         , rnum
                     FROM  table1 t1
                     LEFT  OUTER JOIN
                           (SELECT t2.*
                                 , ROW_NUMBER() OVER() AS rnum
                             FROM  table2 t2
                           ) t2
                       ON  t2.cd_plt   = t1.cd_plt
                       AND t2.pre_name = t1.pre_name
                   ) t
           ) s
     WHERE rnum1 = 1
     ORDER BY
           rnum
    ;
    ------------------------------------------------------------------------------
    
    CD_PLT PRE_NAME    IN_DT      OUT_DT_FLAG
    ------ ----------- ---------- -----------
    ABACC         6065 2011-01-11 Y          
    ABACC         6077 2012-01-10 N          
    ABACC         6000 2011-10-09 N          
    BBBBB         7777 -          Y          
    
      4 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The result of your join contains redundant rows(two or three in your sample data).

    So, it is better to add some more condition to ON clause
    or remove redundant rows from table1.
    (I guessed table1(and table2?) might be result of a subquery or a view,
    because duplicated rows exists.)

    You may want to change the query to produce table1 to remove the redundant rows.


    Here is the result of a query removing outer most WHERE clause from Example 1.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    /* same as example 1 except commented out WHERE clause */
           ) s
    -- WHERE rnum1 = 1
     ORDER BY
           rnum
    ;
    ------------------------------------------------------------------------------
    
    CD_PLT PRE_NAME    IN_DT      OUT_DT_FLAG
    ------ ----------- ---------- -----------
    ABACC         6065 -          Y          
    ABACC         6065 -          Y          
    ABACC         6065 2011-01-11 Y          
    ABACC         6065 2011-01-11 Y          
    ABACC         6077 2012-01-10 N          
    ABACC         6077 2012-01-10 Y          
    ABACC         6077 2011-01-12 Y          
    ABACC         6077 2011-01-12 Y          
    ABACC         6000 2011-10-09 N          
    ABACC         6000 2011-10-09 Y          
    ABACC         6000 2011-10-09 Y          
    ABACC         6000 2010-11-10 Y          
    ABACC         6000 2010-11-10 Y          
    ABACC         6000 2010-11-10 Y          
    ABACC         6000 2009-11-11 Y          
    ABACC         6000 2009-11-11 Y          
    ABACC         6000 2009-11-11 Y          
    BBBBB         7777 -          Y          
    BBBBB         7777 -          Y          
    BBBBB         7777 -          Y          
    BBBBB         7777 -          Y          
    
      21 record(s) selected.

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Great Tonkuma...thanks...working fine...

Posting Permissions

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