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

    Unanswered: select query should fetch past date rows

    Hi;
    Please find the below select query.


    The Past IN_DT records are comes under the 2011 year row..(if the 2011 year row have non-zero value in ITEM_NO in TABLE3,if not that rows are not fetched )

    if the 2011 year row have the ZERo value ITEM_NO means that related 2011 and rows will not fetch from TABLE2

    for that case,It should comes under the next year rows like 2012 (If 2012 row will have non-zero ITEM_NO)

    Code:
    ELECT
           t1.CD_PLT
         , t1.BASE_NAME 
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , p. per_year
         , T3.ITEM_NO
         , t2.IN_DT
         
     FROM
           (SELECT DISTINCT T3.C_YEAR
                  
             FROM  TABLE3   T3 
    
    WHERE T3.C_YEAR >=CHAR(YEAR(current_date))
           ) p(per_year)
     INNER JOIN
           (SELECT DISTINCT
                   *
             FROM  table1
           ) t1
       ON  0=0
    INNER JOIN
    (SELECT DISTINCT *
                  
             FROM  TABLE4   T3 
    
    WHERE ITEM_NO <> 0  )T3
    
    ON  t3.CD_PLT    = T1.CD_PLT
       AND T3.PRE_NAME  = T1.PRE_NAME
       AND T3.BASE_NAME = T1.BASE_NAME
       AND T3.SUF_NAME  = T1.SUF_NAME
       AND t3.C_year = p. per_year
    
     LEFT  OUTER JOIN
           (SELECT t2.*
                 ,MAX( YEAR(IN_DT) , YEAR(current_date) ) AS norm_year
             FROM  table2 t2
           ) t2
       ON  t1.CD_PLT    = T2.CD_PLT
       AND T1.PRE_NAME  = T2.PRE_NAME
       AND T1.BASE_NAME = T2.BASE_NAME
       AND T1.SUF_NAME  = T2.SUF_NAME
       AND  p. per_year=CHAR(T2.NORM_YEAR)
    
    
    
     ORDER BY
           CD_PLT
         , BASE_NAME DESC
         , SUF_NAME
         , PRE_NAME
         , per_year
    ;
    TABLE1
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  
    ----------- ------------ -------------- ---------- 
    ABACC            6065        REWS           AE    
    ABACC            6000        ERWV           DE    
    ABADD            9011        WEST           DR
    TABLE2
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA      IN_DT                     
    ----------- ------------ -------------- ---------- -------    ---------------- 
    ABACC            6065        REWS           AE     21          2009-01-13      
    ABACC            6065        REWS           AE     21          2010-01-12 
         
    ABACC            6000        ERWV           DE     26          2010-11-11 -->THIS ROW SHOULD COME IN THE RESULT SET
    ABACC            6000        ERWV           DE     46          2011-11-11 -->THIS ROW SHOULD COME IN THE RESULT SET 
    ABACC            6000        ERWV           DE     56          2012-11-11    
    
    ABADD            9011        WEST           DR     77          2011-03-01 -->THIS ROW SHOULD COME IN THE RESULT SET
    TABLE3
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME      C_YEAR     ITEM_NO    
    ----------- ------------ -------------- ----------  ------------ -------     
    ABACC            6065        REWS           AE         2010 	   200  
    ABACC            6065        REWS           AE         2011        209 
    ABACC            6065        REWS           AE         2012        310
    ABACC            6065        REWS           AE         2013        100 
    
    ABACC            6000        ERWV           DE         2011        0
    ABACC            6000        ERWV           DE         2012        500
    ABACC            6000        ERWV           DE         2013        600
    ABACC            6000        ERWV           DE         2014        700
    
    ABADD            9011        WEST           DR         2011        0
    ABADD            9011        WEST           DR         2013        1200
    CURRENT RESULT SET
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR   ITEM_NO          IN_DT             
    ----------- ------------ -------------- ----------  ------------     -------   -----------     --------------    
    ABACC            6065        REWS           AE             21           2011 	209              2009-01-13 
    ABACC            6065        REWS           AE             21           2011    209              2010-01-12       
    ABACC            6065        REWS           AE             -            2012    310                -                
    ABACC            6065        REWS           AE             -            2013    100                - 
    
    
    ABACC            6000        ERWV           DE             56           2012    500             2012-11-11 
    ABACC            6000        ERWV           DE             -            2013    600                -       
    ABACC            6000        ERWV           DE             -            2014    700                -
    
    ABADD            9011        WEST           DR             77           2013   1200                -
    I am expecting the result set like below

    EXPECTED RESULT SET
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR   ITEM_NO          IN_DT             
    ----------- ------------ -------------- ----------  ------------     -------   -----------     --------------    
    ABACC            6065        REWS           AE             21           2011 	209              2009-01-13        
    ABACC            6065        REWS           AE             -            2012    310                -                
    ABACC            6065        REWS           AE             -            2013    100                - 
    
    ABACC            6000        ERWV           DE             26           2012    500             2010-11-11  
    ABACC            6000        ERWV           DE             46           2012    500             2011-11-11 
    ABACC            6000        ERWV           DE             56           2012    500             2012-11-11 
    ABACC            6000        ERWV           DE             -            2013    600                -       
    ABACC            6000        ERWV           DE             -            2014    700                -
    
    ABADD            9011        WEST           DR             77           2013   1200             2011-03-01
    Please help.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't find the reason to select a row in a group with (CD_PLT = ABACC and BASE_NAME = 6065).

    TABLE2(adjust blanks for readability)
    Code:
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA IN_DT                     
    ------ --------- -------- -------- ------- ---------- 
    ABACC  6065      REWS     AE       21      2009-01-13 --> Why took this row?
    ABACC  6065      REWS     AE       21      2010-01-12 --> Why didn't took this row?
    Last edited by tonkuma; 12-28-11 at 09:51.

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

    Apology for that..Typo mistake...that row should be in the result set..

    Pl help..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    > that row
    What row?

    Do you want to say that both of rows(IN_DT = 2009-01-13 and 2010-01-12) should be in the result set?

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    yes tonkuma..

    Below is the Expected result set
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR   ITEM_NO          IN_DT             
    ----------- ------------ -------------- ----------  ------------     -------   -----------     --------------    
    ABACC            6065        REWS           AE             21           2011 	209              2009-01-13 
    ABACC            6065        REWS           AE             21           2011 	209              2010-01-12        
    ABACC            6065        REWS           AE             -            2012    310                -                
    ABACC            6065        REWS           AE             -            2013    100                - 
    
    ABACC            6000        ERWV           DE             26           2012    500             2010-11-11  
    ABACC            6000        ERWV           DE             46           2012    500             2011-11-11 
    ABACC            6000        ERWV           DE             56           2012    500             2012-11-11 
    ABACC            6000        ERWV           DE             -            2013    600                -       
    ABACC            6000        ERWV           DE             -            2014    700                -
    
    ABADD            9011        WEST           DR             77           2013   1200             2011-03-01
    Thanks;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into rows in table2 and table3,
    columns PRE_NAME and SUF_NAME are dependent on pair of columns(CD_PLT, BASE_NAME).
    The values of PRE_NAME and SUF_NAME are in table1.

    So, table2 and table3 should not have columns PRE_NAME and SUF_NAME viewed from normalization.

    I thought that you would have two options.
    (1) remove columns PRE_NAME and SUF_NAME from table2 and table3.
    Then join table1 to get the values of the columns.

    (2) ignore table1 in this query.
    Join table2 and table3, take values of PRE_NAME and SUF_NAME from table3.
    join conditions "T2.PRE_NAME = T3.PRE_NAME" and "T2.SUF_NAME = T3.SUF_NAME" are not necessary.


    Here is an example of option(1).

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TABLE1
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' )
    )
    , TABLE2
    ( CD_PLT , BASE_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 21 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 26 , '2010-11-11' )
    , ( 'ABACC' , '6000' , 46 , '2011-11-11' )
    , ( 'ABACC' , '6000' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 77 , '2011-03-01' )
    )
    , TABLE3
    ( CD_PLT , BASE_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , '2010' ,  200 )
    , ( 'ABACC' , '6065' , '2011' ,  209 )
    , ( 'ABACC' , '6065' , '2012' ,  310 )
    , ( 'ABACC' , '6065' , '2013' ,  100 )
    , ( 'ABACC' , '6000' , '2011' ,    0 )
    , ( 'ABACC' , '6000' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , '2011' ,    0 )
    , ( 'ABADD' , '9011' , '2013' , 1200 )
    )
    SELECT t3.CD_PLT
         , t3.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t3.C_YEAR  AS per_year
         , t3.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t3.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE3 t3
             WHERE t3.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t3.ITEM_NO >  0
           ) t3
     LEFT  OUTER JOIN
           TABLE2 t2
       ON  t2.CD_PLT    = t3.CD_PLT
       AND t2.BASE_NAME = t3.BASE_NAME
       AND
         (     CHAR( YEAR(t2.IN_DT) ) = t3.C_YEAR
           OR  t3.rnum                = 1
           AND CHAR( YEAR(t2.IN_DT) ) < t3.C_YEAR
         )
     INNER JOIN
           TABLE1 t1
       ON  t1.CD_PLT    = t3.CD_PLT
       AND t1.BASE_NAME = t3.BASE_NAME
     ORDER BY
           CD_PLT
         , BASE_NAME DESC
         , per_year
         , IN_DT
    ;
    ------------------------------------------------------------------------------
    
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA     PER_YEAR ITEM_NO     IN_DT     
    ------ --------- -------- -------- ----------- -------- ----------- ----------
    ABACC  6065      REWS     AE                21 2011             209 2009-01-13
    ABACC  6065      REWS     AE                21 2011             209 2010-01-12
    ABACC  6065      REWS     AE                 - 2012             310 -         
    ABACC  6065      REWS     AE                 - 2013             100 -         
    ABACC  6000      ERWV     DE                26 2012             500 2010-11-11
    ABACC  6000      ERWV     DE                46 2012             500 2011-11-11
    ABACC  6000      ERWV     DE                56 2012             500 2012-11-11
    ABACC  6000      ERWV     DE                 - 2013             600 -         
    ABACC  6000      ERWV     DE                 - 2014             700 -         
    ABADD  9011      WEST     DR                77 2013            1200 2011-03-01
    
      10 record(s) selected.
    Last edited by tonkuma; 12-29-11 at 04:24. Reason: Remove from "table2 or" in option(2) and add cte TABLE1 to Example 1 and remove comments for TABLE1.

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    Excellent...thanks for the reply..working fine..

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Hi;
    we should not ignore the TABLE1...we must use in the joins...The result set should have unmatched rows also from Table 1 so that i was using Left outer join with Table2 in my code..
    Thanks;

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    There is no reason to use table1 from your published data.

    You might have more other data of tables which would prove your insistence.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example of option(2).
    Table1 is not necessary.

    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-11-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2011-03-01' )
    )
    , TABLE3
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2010' ,  200 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2011' ,  209 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  310 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' ,  100 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2011' ,    0 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2011' ,    0 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
    )
    SELECT t3.CD_PLT
         , t3.BASE_NAME
         , t3.PRE_NAME
         , t3.SUF_NAME
         , t2.WKLY_CA
         , t3.C_YEAR  AS per_year
         , t3.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t3.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE3 t3
             WHERE t3.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t3.ITEM_NO >  0
           ) t3
     LEFT  OUTER JOIN
           TABLE2 t2
       ON  t2.CD_PLT    = t3.CD_PLT
       AND t2.BASE_NAME = t3.BASE_NAME
       AND
         (     CHAR( YEAR(t2.IN_DT) ) = t3.C_YEAR
           OR  t3.rnum                = 1
           AND CHAR( YEAR(t2.IN_DT) ) < t3.C_YEAR
         )
     ORDER BY
           CD_PLT
         , BASE_NAME DESC
         , per_year
    ;
    ------------------------------------------------------------------------------
    
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA     PER_YEAR ITEM_NO     IN_DT     
    ------ --------- -------- -------- ----------- -------- ----------- ----------
    ABACC  6065      REWS     AE                21 2011             209 2009-01-13
    ABACC  6065      REWS     AE                21 2011             209 2010-01-12
    ABACC  6065      REWS     AE                 - 2012             310 -         
    ABACC  6065      REWS     AE                 - 2013             100 -         
    ABACC  6000      ERWV     DE                26 2012             500 2010-11-11
    ABACC  6000      ERWV     DE                46 2012             500 2011-11-11
    ABACC  6000      ERWV     DE                56 2012             500 2012-11-11
    ABACC  6000      ERWV     DE                 - 2013             600 -         
    ABACC  6000      ERWV     DE                 - 2014             700 -         
    ABADD  9011      WEST     DR                77 2013            1200 2011-03-01
    
      10 record(s) selected.

Posting Permissions

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