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

    Answered: merging the rows

    Hi

    DB2/ZOS 9.5

    Please help me to merging the rows and construct one row

    The base table has the 4 quarter values for all the years,we need to construct one row based on the given quarter range
    Code:
    Base_Table
    
     Id_Val   year   q1   q2   q3   q4
    ABC       2011   10   20   30   40 
    ABC       2012   11   19   13   24 
    ABC       2013   45   12   55   65
    we want the quarter range starts from '2011-04-15' ends at '2013-01-15'

    based on the start date range the quarter value will be populated
    in this example start quarter is Q2 in year of 2011 ,so we are starts from 2011 q2 as Q1 in resultset
    and ends at Q1 of year 2013,So it will be the End quarter Q8
    Like that Quarter range(count Q1 to Q12) will be vary based the date range
    Code:
    Expected result set will be
    
    Id_val   q1  q2  q3 q4 q5 q6 q7 q8
    ABC      20  30  40 11 19 13 24 45
    Thanks,

  2. Best Answer
    Posted by mark.bb

    "
    Code:
    with t (Id_Val, year, q1, q2, q3, q4) as (
    select 'ABC', 2011, 10, 20, 30, 40 from sysibm.sysdummy1
    union all
    select 'ABC', 2012, 11, 19, 13, 24 from sysibm.sysdummy1
    union all
    select 'ABC', 2013, 45, 12, 55, 65 from sysibm.sysdummy1
    )
    select 
      sum(case year when 2011 then q3 end) as q1
    , sum(case year when 2011 then q4 end) as q2
    , sum(case year when 2012 then q1 end) as q3
    , sum(case year when 2012 then q2 end) as q4
    , sum(case year when 2012 then q3 end) as q5
    , sum(case year when 2012 then q4 end) as q6
    , 0 as q7
    , 0 as q8
    from t
    group by id_val
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Hi,

    You must construct such a query dynamically like this:
    Code:
    with t (Id_Val, year, q1, q2, q3, q4) as (
    select 'ABC', 2011, 10, 20, 30, 40 from sysibm.sysdummy1
    union all
    select 'ABC', 2012, 11, 19, 13, 24 from sysibm.sysdummy1
    union all
    select 'ABC', 2013, 45, 12, 55, 65 from sysibm.sysdummy1
    )
    select 
      sum(case year when 2011 then q2 end) as q1
    , sum(case year when 2011 then q3 end) as q2
    , sum(case year when 2011 then q4 end) as q3
    , sum(case year when 2012 then q1 end) as q4
    , sum(case year when 2012 then q2 end) as q5
    , sum(case year when 2012 then q3 end) as q6
    , sum(case year when 2012 then q4 end) as q7
    , sum(case year when 2013 then q1 end) as q8
    from t
    group by id_val
    Regards,
    Mark.

  4. #3
    Join Date
    Sep 2011
    Posts
    220
    Thank you for the reply,
    Suppose if we have the date range would be
    '2011-08-14' ends at '2012-12-15' ( Q1 will be the Q3 of year 2011 ,starts there and ends Q6 will be Q4 of year 2012)
    Code:
    Expected result set will be
    Id_val   q1  q2  q3 q4 q5 q6 q7 q8
    ABC      30  40  11 19 13 24 0   0

  5. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Code:
    with t (Id_Val, year, q1, q2, q3, q4) as (
    select 'ABC', 2011, 10, 20, 30, 40 from sysibm.sysdummy1
    union all
    select 'ABC', 2012, 11, 19, 13, 24 from sysibm.sysdummy1
    union all
    select 'ABC', 2013, 45, 12, 55, 65 from sysibm.sysdummy1
    )
    select 
      sum(case year when 2011 then q3 end) as q1
    , sum(case year when 2011 then q4 end) as q2
    , sum(case year when 2012 then q1 end) as q3
    , sum(case year when 2012 then q2 end) as q4
    , sum(case year when 2012 then q3 end) as q5
    , sum(case year when 2012 then q4 end) as q6
    , 0 as q7
    , 0 as q8
    from t
    group by id_val
    Regards,
    Mark.

  6. #5
    Join Date
    Sep 2011
    Posts
    220
    Thank you..

    How find the number of quarters between two dates

    Ex: 15-01-2011 to 19-05-2012
    Result will be 6 quarters
    Please help me to write in the query

  7. #6
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    110
    Provided Answers: 13
    Quote Originally Posted by Billa007 View Post
    How find the number of quarters between two dates

    Ex: 15-01-2011 to 19-05-2012
    Result will be 6 quarters
    Please help me to write in the query
    The standard db2 function shows 5.
    TIMESTAMPDIFF(128, CHAR(TIMESTAMP('2012-05-19-00.00.00') - TIMESTAMP('2011-01-15-00.00.00')))
    Regards,
    Mark.

Posting Permissions

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