Results 1 to 5 of 5

Thread: date logic

  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: date logic

    Hi,

    DB2 V9.1 Z/os

    Please find the Base_Table row and help me to write a query how to get the
    'Expected Output' as below

    Code:
    SELECT 
     PLT_NM,
    FIRST_NO,
    SECOND_NO,
    THIRD_NO,
    WORK_DT_FR,
    WORK_DT_TO
    
    FROM BASE_TABLE
    Code:
    BASE_TABLE
    
    PLT_NM   FIRST_NO    SECOND_NO  THIRD_NO  WORK_DT_FR   WORK_DT_TO
    AAA       A12          RES1      A         2004-03-04    2010-01-02
    AAA       A12          RES1      B         2010-01-02    2012-12-31
    AAA       A12          RES1      C	   2012-12-31    2015-12-31
    AAA       B12          XXX5      FR        2004-03-04    2016-01-02
    AAA       VER          YYY5      RE        2010-01-02    2015-12-31
    AAA       GTR          FRES      JIY	   2012-12-31    2015-12-31
    AAA       B31          TER1      AB        2011-12-31    2013-02-24 
    AAA       B31          TER1      AC        2013-02-24    2016-11-30 
    AAA       B31          TER1      BB        2011-01-21    2013-10-30 
    AAA       G1R          FTR1      HA1       2010-11-01    2012-02-21 
    AAA       G1R          FTR1      HA2       2012-02-21    2014-10-30
    plese have a look on the base table data

    the THIRD_NO column row are converted to next level by the alphabetical order
    and the WORK_DT_FR is equal to prior row WORK_DT_TO

    I am expectiong the results like,If the FIRST_NO and SECOND_NO is same
    and check the WORK_DT_TO value it should be in the WORK_DT_FR of next
    row
    Code:
             
    EXPECTED OUTPUT
    
    PLT_NM   FIRST_NO    SECOND_NO  THIRD_NO  WORK_DT_FR   WORK_DT_TO
    AAA       A12          RES1      A         2004-03-04    2010-01-02
    AAA       A12          RES1      B         2010-01-02    2012-12-31
    AAA       A12          RES1      C	   2012-12-31    2015-12-31
    AAA       B31          TER1      AB        2011-12-31    2013-02-24 
    AAA       B31          TER1      AC        2013-02-24    2016-11-30 
    AAA       G1R          FTR1      HA1       2010-11-01    2012-02-21 
    AAA       G1R          FTR1      HA2       2012-02-21    2014-10-30

    For Ex1:
    consider below set of rows from Base Table
    Code:
    PLT_NM   FIRST_NO    SECOND_NO  THIRD_NO  WORK_DT_FR   WORK_DT_TO
    AAA       B31          TER1      AB        2011-12-31    2013-02-24 
    AAA       B31          TER1      AC        2013-02-24    2016-11-30 
    AAA       B31          TER1      BB        2011-01-21    2013-10-30
    Above FIRST_NO and SECOND_NO value is same and look the THIRD_NO ,it is
    different from prior record, so look into the WORK_DT_TO value of prior row
    the same value row should be returned to result set,But the third row sholuld not
    come to the result set because second row WORK_DT_TO value is not sync with
    third row WORK_DT_FR value

    Expected result set based on this Ex1
    Code:
    PLT_NM   FIRST_NO    SECOND_NO  THIRD_NO  WORK_DT_FR   WORK_DT_TO
    AAA       B31          TER1      AB        2011-12-31    2013-02-24 
    AAA       B31          TER1      AC        2013-02-24    2016-11-30

    please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try

    Code:
    WITH
     numbered_base_table AS (
    SELECT t.*
         , ROW_NUMBER()
              OVER( PARTITION BY PLT_NM , FIRST_NO , SECOND_NO
                        ORDER BY THIRD_NO
                  ) AS row_num
     FROM  base_table AS t
    )
    SELECT PLT_NM   , FIRST_NO   , SECOND_NO
         , THIRD_NO , WORK_DT_FR , WORK_DT_TO
     FROM  numbered_base_table AS a
     WHERE EXISTS
           (SELECT 0
             FROM  numbered_base_table AS b
             WHERE b.PLT_NM    = a.PLT_NM
              AND  b.FIRST_NO  = a.FIRST_NO
              AND  b.SECOND_NO = a.SECOND_NO
              AND
              (    b.row_num    = a.row_num - 1
               AND b.WORK_DT_TO = a.WORK_DT_FR
               OR
                   b.row_num    = a.row_num + 1
               AND b.WORK_DT_FR = a.WORK_DT_TO
              )
           )
    ;

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma,working fine..can you please say..How it is possible to use b.row_num ?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... How it is possible to use b.row_num ?
    I couldn't understand why you asked the question.

    "row_num" is a column of "numbered_base_table AS b".
    So, I thought that no problem was there to use it as "b.row_num".

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Example 2:
    Code:
    WITH
     numbered_base_table AS (
    SELECT t.*
         , ROW_NUMBER()
              OVER( PARTITION BY PLT_NM , FIRST_NO , SECOND_NO
                        ORDER BY THIRD_NO
                  ) AS row_num
     FROM  base_table AS t
    )
    SELECT DISTINCT
           a.PLT_NM   , a.FIRST_NO   , a.SECOND_NO
         , a.THIRD_NO , a.WORK_DT_FR , a.WORK_DT_TO
     FROM  numbered_base_table AS a
     INNER JOIN
           numbered_base_table AS b
      ON   b.PLT_NM    = a.PLT_NM
      AND  b.FIRST_NO  = a.FIRST_NO
      AND  b.SECOND_NO = a.SECOND_NO
      AND
      (    b.row_num = a.row_num - 1
       AND b.WORK_DT_TO = a.WORK_DT_FR
       OR
           b.row_num = a.row_num + 1
       AND b.WORK_DT_FR = a.WORK_DT_TO
      )
     ORDER BY
           a.PLT_NM   , a.FIRST_NO   , a.SECOND_NO
    ;

Posting Permissions

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