Results 1 to 9 of 9
  1. #1
    Join Date
    May 2016
    Posts
    5

    Post Unanswered: Merge rows based on derived data in DB2

    Hi

    I have a derived data from 4 sub select in the below form which I want to merge/combine as below. Wondering how to do it.

    ST_DT END_DT STUS FLAG
    01/01/2015 01/31/2015 02 F
    02/01/2015 02/28/2015 08 F
    03/01/2015 03/31/2015 08 F
    04/01/2015 04/30/2015 02 F
    05/01/2015 05/31/2015 06 P
    06/01/2015 06/30/2015 06 P
    07/01/2015 07/31/2015 08 F
    08/01/2015 08/31/2015 08 F
    09/01/2015 09/30/2015 08 F
    10/01/2015 10/31/2015 08 F
    11/01/2015 11/30/2015 08 F
    12/01/2015 12/31/2015 00 N

    Output needed is

    ST_DT END_DT STUS FLAG
    01/01/2015 01/31/2015 02 F
    02/01/2015 03/31/2015 08 F
    04/01/2015 04/30/2015 02 F
    05/01/2015 06/30/2015 06 P
    07/01/2015 11/30/2015 08 F
    12/01/2015 12/31/2015 00 N

    thanks
    MSTP

  2. #2
    Join Date
    Jan 2003
    Posts
    4,286
    Provided Answers: 5
    Code:
    SELECT min(ST_DT) as ST_DT,max(END_DT) as END_DT,STUS,FLAG
    FROM MyTable
    GROUP BY STUS,FLAG
    Andy

  3. #3
    Join Date
    May 2016
    Posts
    5
    I tried the same before posting but the results were as below which isn't as the output as needed.

    ST_DT END_DT STUS FLAG
    01/01/2015 04/30/2015 02 F
    02/01/2015 11/30/2015 08 F
    05/01/2015 06/30/2015 06 P
    12/01/2015 12/31/2015 00 N

    MSTP

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select min(ST_DT) ST_DT, max(END_DT) END_DT, STUS, FLAG
    from (
    select date(ST_DT) ST_DT, date(END_DT) END_DT, STUS, FLAG
    , sum(
    case when ST_DT = lag(date(END_DT)) over(partition by STUS, FLAG order by date(END_DT)) + 1 day then 0 else 1 end
    ) over (partition by STUS, FLAG order by date(END_DT)) grp
    from table(values 
      ('01/01/2015', '01/31/2015', '02', 'F')
    , ('02/01/2015', '02/28/2015', '08', 'F')
    , ('03/01/2015', '03/31/2015', '08', 'F')
    , ('04/01/2015', '04/30/2015', '02', 'F')
    , ('05/01/2015', '05/31/2015', '06', 'P')
    , ('06/01/2015', '06/30/2015', '06', 'P')
    , ('07/01/2015', '07/31/2015', '08', 'F')
    , ('08/01/2015', '08/31/2015', '08', 'F')
    , ('09/01/2015', '09/30/2015', '08', 'F')
    , ('10/01/2015', '10/31/2015', '08', 'F')
    , ('11/01/2015', '11/30/2015', '08', 'F')
    , ('12/01/2015', '12/31/2015', '00', 'N')
    ) t (ST_DT, END_DT, STUS, FLAG)
    )
    group by STUS, FLAG, GRP
    Regards,
    Mark.

  5. #5
    Join Date
    May 2016
    Posts
    5
    Quote Originally Posted by mark.b View Post
    Hi,

    try this:
    Code:
    select min(ST_DT) ST_DT, max(END_DT) END_DT, STUS, FLAG
    from (
    select date(ST_DT) ST_DT, date(END_DT) END_DT, STUS, FLAG
    , sum(
    case when ST_DT = lag(date(END_DT)) over(partition by STUS, FLAG order by date(END_DT)) + 1 day then 0 else 1 end
    ) over (partition by STUS, FLAG order by date(END_DT)) grp
    from table(values 
      ('01/01/2015', '01/31/2015', '02', 'F')
    , ('02/01/2015', '02/28/2015', '08', 'F')
    , ('03/01/2015', '03/31/2015', '08', 'F')
    , ('04/01/2015', '04/30/2015', '02', 'F')
    , ('05/01/2015', '05/31/2015', '06', 'P')
    , ('06/01/2015', '06/30/2015', '06', 'P')
    , ('07/01/2015', '07/31/2015', '08', 'F')
    , ('08/01/2015', '08/31/2015', '08', 'F')
    , ('09/01/2015', '09/30/2015', '08', 'F')
    , ('10/01/2015', '10/31/2015', '08', 'F')
    , ('11/01/2015', '11/30/2015', '08', 'F')
    , ('12/01/2015', '12/31/2015', '00', 'N')
    ) t (ST_DT, END_DT, STUS, FLAG)
    )
    group by STUS, FLAG, GRP

    Thanks Mark but unfortunately we are on DB2 Z/OS V10 and this LAG Function is unavailable for it. Please let me know if their is any other word around.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    max(date(END_DT)) over(partition by STUS, FLAG order by date(END_DT) rows between 1 preceding and 1 preceding)
    instead of lag(...) over (...)
    Regards,
    Mark.

  7. #7
    Join Date
    May 2016
    Posts
    5
    Quote Originally Posted by mark.b View Post
    max(date(END_DT)) over(partition by STUS, FLAG order by date(END_DT) rows between 1 preceding and 1 preceding)
    instead of lag(...) over (...)
    Thanks Mark I used the MAX function and the rest of the syntax as mentioned while executing its giving SQLCODE -112 SQLSTATE 42607. Not sure how to resolve it.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Seems that zDB2 doesn't allow olap inside olap.
    Try this:
    Code:
    with t(ST_DT, END_DT, STUS, FLAG) as (values 
      ('01/01/2015', '01/31/2015', '02', 'F')
    , ('02/01/2015', '02/28/2015', '08', 'F')
    , ('03/01/2015', '03/31/2015', '08', 'F')
    , ('04/01/2015', '04/30/2015', '02', 'F')
    , ('05/01/2015', '05/31/2015', '06', 'P')
    , ('06/01/2015', '06/30/2015', '06', 'P')
    , ('07/01/2015', '07/31/2015', '08', 'F')
    , ('08/01/2015', '08/31/2015', '08', 'F')
    , ('09/01/2015', '09/30/2015', '08', 'F')
    , ('10/01/2015', '10/31/2015', '08', 'F')
    , ('11/01/2015', '11/30/2015', '08', 'F')
    , ('12/01/2015', '12/31/2015', '00', 'N')
    )
    select min(ST_DT) ST_DT, max(END_DT) END_DT, STUS, FLAG
    from (
    select ST_DT, END_DT, STUS, FLAG, sum(cnt) over (partition by STUS, FLAG order by END_DT) grp
    from (
    select date(to_date(ST_DT, 'MM/DD/YYYY')) ST_DT, date(to_date(END_DT, 'MM/DD/YYYY')) END_DT, STUS, FLAG
    , case when date(to_date(ST_DT, 'MM/DD/YYYY')) = 
    max(date(to_date(END_DT, 'MM/DD/YYYY'))) over(partition by STUS, FLAG order by date(to_date(END_DT, 'MM/DD/YYYY')) rows between 1 preceding and 1 preceding) 
    + 1 day then 0 else 1 end cnt
    from t
    )
    ) t
    group by STUS, FLAG, GRP
    Regards,
    Mark.

  9. #9
    Join Date
    May 2016
    Posts
    5

    Thumbs up

    Quote Originally Posted by mark.b View Post
    Seems that zDB2 doesn't allow olap inside olap.
    Try this:
    Code:
    with t(ST_DT, END_DT, STUS, FLAG) as (values 
      ('01/01/2015', '01/31/2015', '02', 'F')
    , ('02/01/2015', '02/28/2015', '08', 'F')
    , ('03/01/2015', '03/31/2015', '08', 'F')
    , ('04/01/2015', '04/30/2015', '02', 'F')
    , ('05/01/2015', '05/31/2015', '06', 'P')
    , ('06/01/2015', '06/30/2015', '06', 'P')
    , ('07/01/2015', '07/31/2015', '08', 'F')
    , ('08/01/2015', '08/31/2015', '08', 'F')
    , ('09/01/2015', '09/30/2015', '08', 'F')
    , ('10/01/2015', '10/31/2015', '08', 'F')
    , ('11/01/2015', '11/30/2015', '08', 'F')
    , ('12/01/2015', '12/31/2015', '00', 'N')
    )
    select min(ST_DT) ST_DT, max(END_DT) END_DT, STUS, FLAG
    from (
    select ST_DT, END_DT, STUS, FLAG, sum(cnt) over (partition by STUS, FLAG order by END_DT) grp
    from (
    select date(to_date(ST_DT, 'MM/DD/YYYY')) ST_DT, date(to_date(END_DT, 'MM/DD/YYYY')) END_DT, STUS, FLAG
    , case when date(to_date(ST_DT, 'MM/DD/YYYY')) = 
    max(date(to_date(END_DT, 'MM/DD/YYYY'))) over(partition by STUS, FLAG order by date(to_date(END_DT, 'MM/DD/YYYY')) rows between 1 preceding and 1 preceding) 
    + 1 day then 0 else 1 end cnt
    from t
    )
    ) t
    group by STUS, FLAG, GRP


    Thanks Mark for the updated SQL it worked like a charm. All my testing when thru successfully. Changing the threaded to Answered.

    thanks
    MSTP

Posting Permissions

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