Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: rows clubbed based on Quarters

    Hi all,

    Please help me to write a query to get THE expected result set

    DB2 V9.1/ZOS
    URN_NAME CHAR(5)
    ST_DATE DATE
    ST_QNTY INTEGER


    The same URN_NAME has to be clubbed with one row with quarter values

    Ex1:
    ('AAA','B1111','2014-03-31',10)
    ('AAA','B1111','2014-12-31',10)

    The same URN_NAME is giving ST_QNTY as 10 for differnt quarters( q1 and q4)
    So the row sholud come like below

    URN URN_NAME Q1 Q2 Q3 Q4

    AAA B1111 10 0 0 10

    Ex2:
    ('BBB','C2222','2014-08-31',10)
    ('BBB','C2222','2014-10-31',6)

    Result
    BBB C2222 0 0 10 6
    Code:
    WITH TEMP(URN,URN_NAME,ST_DATE,ST_QNTY)
    VALUES(
    ('AAA','A1111','2014-05-31',10) 
    ('AAA','B1111','2014-03-31',10)       
    ('AAA','B1111','2014-12-31',10)       
    ('AAA','C2222','2014-08-31',10)      
    ('BBB','A1111','2014-06-27',10)
    ('BBB','B1111','2014-03-31',5)      
    ('BBB','C2222','2014-08-31',10)
    ('BBB','C2222','2014-10-31',6)
    ('BBB','D3333','2014-10-31',2)      
    ('BBB','A2222','2014-03-31',5)
    ('BBB','E4444','2014-10-31',2)
    AS
    SELECT URN
    ,URN_NAME
    ,ST_DATE
    ,CASE WHEN QUARTER(ST_DATE) = 1
         THEN ST_QNTY  
     END AS Q1
    ,CASE WHEN QUARTER(ST_DATE) = 2
         THEN ST_QNTY  
     END AS Q2
    ,CASE WHEN QUARTER(ST_DATE) = 3
         THEN ST_QNTY  
     END AS Q3
    ,CASE WHEN QUARTER(ST_DATE) = 4
         THEN ST_QNTY  
     END AS Q4
     
    
    FROM TRAN_TABLE
    GROUP BY
    ,URN_NAME
    ,ST_DATE
    ,ST_QNTY
    
    ORDER BY
    
    ST_DATE
    )
    EXPECTED RESULT SET

    Code:
    URN   URN_NAME   Q1   Q2   Q3  Q4   
    AAA    B1111     10   0    0   10      
    AAA    A1111      0   10   0    0       
    AAA    C2222      0   0    10   0       
    
    
    BBB    A2222     5    0    0    0     
    BBB    B1111     5    0    0    0     
    BBB    A1111     0    10   0    0        
    BBB    C2222     0    0    10   6
    BBB    D3333     0    0    0    2
    BBB    E4444     0    0    0    2
    Thanks,

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Start by getting the syntax for your example query correct:

    Code:
    with temp ... as (
        values (...)
            ,  (...)
    
    ) select ...
      from temp
    Once done, think about what you could do if you want 0 instead of null in your case statement

    In your example you have a column thats not in the expected output, remove it from the query

    Finally, what can you do to aggregate quarters over the missing column?

    Done
    --
    Lennart

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions(for your requirements and sample data)...

    (1) Are there more than one row for a quarter?
    If there are more than one row for a quarter, what results do you want for the quarter?
    SUM(ST_QNTY)? AVG(ST_QNTY)? ...

    (2) Are there some years for an URN_NAME?

    (3) Are there more than two quarters for an URN_NAME?
    Last edited by tonkuma; 06-15-14 at 05:11.

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Thanks,
    (1) Are there more than one row for a quarter?
    If there are more than one row for a quarter, what results do you want for the quarter?
    SUM(ST_QNTY)? AVG(ST_QNTY)? ...

    yes..the sum of ST_QNTY < = 10


    (2) Are there some years for an URN_NAME?

    yes..i will use Year filter in the Where clause.. like
    where year(st_date)=2014

    (3) Are there more than two quarters for an URN_NAME?

    yes
    in sample data
    below are Quarter 4 rows

    ('BBB','C2222','2014-10-31',6)
    ('BBB','D3333','2014-10-31',2)
    ('BBB','E4444','2014-10-31',2)

    some cases

    any quarters will not available
    like
    ('CCC','YY222','2014-05-31',10)
    ('CCC','ZZ444','2014-12-31',10)
    It has quarter 2 and quarter 4 data..

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    some cases

    any quarters will not available
    like
    ('CCC','YY222','2014-05-31',10)
    ('CCC','ZZ444','2014-12-31',10)
    It has quarter 2 and quarter 4 data..
    sorry typo error
    any rows wil have quarters
    like
    ('CCC','YY222','2014-05-31',10)
    ('CCC','ZZ444','2014-12-31',10)

    for above case,is it possible to populate previous quarter values to next quarter like below(expected result set for the case)
    Code:
    CCC YY222    0  10  10  0
    CCC ZZ444    0  0    0   10
    Quarter 2 values has to be populated to quarter 3

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Thanks,
    (1) Are there more than one row for a quarter?
    If there are more than one row for a quarter, what results do you want for the quarter?
    SUM(ST_QNTY)? AVG(ST_QNTY)? ...

    yes..the sum of ST_QNTY < = 10
    What means "< = 10"?

    (3) Are there more than two quarters for an URN_NAME?

    yes
    in sample data
    below are Quarter 4 rows

    ('BBB','C2222','2014-10-31',6)
    ('BBB','D3333','2014-10-31',2)
    ('BBB','E4444','2014-10-31',2)
    These rows are different URN_NAMEs and same quarter.
    So, it is not the example of "more than two quarters for an URN_NAME".

    An example of such case might be like...
    ('CCC','F1234','2014-03-31',6)
    ('CCC','F1234','2014-04-27',2)
    ('CCC','F1234','2014-10-31',3)

    and expected result might be...
    Code:
    URN URN_NAME Q1 Q2 Q3 Q4
    CCC F1234    6  2      3
    Is the expected result right?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    ...

    any rows wil have quarters
    like
    ('CCC','YY222','2014-05-31',10)
    ('CCC','ZZ444','2014-12-31',10)

    for above case,is it possible to populate previous quarter values to next quarter like below(expected result set for the case)
    Code:
    CCC YY222    0  10  10  0
    CCC ZZ444    0  0    0   10
    Quarter 2 values has to be populated to quarter 3
    In what condition, is it neccesary to populate previous quarter values to next quarter?

    This might be a new requirement which was not mentioned before.
    Right?


    Please investigate your requirements more carefully and describe your requirements more comprehensively,
    by using considerable various sample data including some exceptional cases(and expected results from the data),
    before asking your issue.

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply,
    What means "< = 10"?

    The sum should be 10,but very rare occurrence sum will be less than 10
    In some scenarios if URN does not have Quarter 2 we can populate the Quater1 values to q2..and q3 and q4 like versa

    An example of such case might be like...
    ('CCC','F1234','2014-03-31',6)
    ('CCC','F1234','2014-04-27',2)
    ('CCC','F1234','2014-10-31',3)

    and expected result might be...
    Code:
    URN URN_NAME Q1 Q2 Q3 Q4
    CCC F1234 6 2 3
    Is the expected result right?
    correct ,this is the expected result,but the sum of every quarter should be 10 for URN
    for that example input data might be like this'

    ('CCC','F1234','2014-03-31',6)
    ('CCC','F1234','2014-04-27',2)
    ('CCC','F1234','2014-10-31',3)
    ('CCC','G1234','2014-03-31',4)
    ('CCC','G1234','2014-04-27',8)
    ('CCC','G1234','2014-10-31',7)
    so expected result set like be

    CCC F1234 6 2 0 3
    CCC G1234 4 8 0 7

    In what condition, is it neccesary to populate previous quarter values to next quarter?

    This might be a new requirement which was not mentioned before.
    Right?

    If any quarter (except first quarter) is not having values means ,we should bring it from previous quarters like below
    consider previous example

    CCC F1234 6 2 0 3
    CCC G1234 4 8 0 7

    the result set is not having Q3 value so we can bring the Q2 value to Q3

    CCC F1234 6 2 2 3
    CCC G1234 4 8 8 7

    more examples

    DDD H1111 2014-02-27 10
    DDD J4444 2014-12-15 10

    EEE R2343 2014-05-11 10
    EEE G3333 2014-09-09 10

    EXPECTED RESULT SET

    DDD H1111 10 10 10 0
    DDD J4444 0 0 0 10

    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 10

    BOLDED IS POPULATED VALUE

    sorry,I thought will ask later,because it occurs very rare scenarios only

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    more examples

    DDD H1111 2014-02-27 10
    DDD J4444 2014-12-15 10

    EEE R2343 2014-05-11 10
    EEE G3333 2014-09-09 10

    EXPECTED RESULT SET

    DDD H1111 10 10 10 0
    DDD J4444 0 0 0 10

    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 10
    Why not the following?
    EEE R2343 0 10 0 10

  10. #10
    Join Date
    Sep 2011
    Posts
    220
    Thanks
    Quote:
    more examples

    DDD H1111 2014-02-27 10
    DDD J4444 2014-12-15 10

    EEE R2343 2014-05-11 10
    EEE G3333 2014-09-09 10

    EXPECTED RESULT SET

    DDD H1111 10 10 10 0
    DDD J4444 0 0 0 10

    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 10
    Why not the following?
    EEE R2343 0 10 0 10
    Because for Quarter 3 the URN(EEE) has another URN_NAME like G3333

    for every URN is will be getting total of 10 from different or one URN_NAME for every quarters
    see the example

    EEE R2343 2014-05-11 10
    EEE G3333 2014-09-09 10

    for EEE is getting total of 10 from R2343 for quarter 2
    for EEE is getting total of 10 from G3333 for quarter 3 and quarter 4
    Means that, latest URN_NAME is valid for future quarters also

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Because for Quarter 3 the URN(EEE) has another URN_NAME like G3333
    I asked for Quarter 4, not for Quarter 3.

    If sum of ST_QNTY for an URN should be equal or less than 10,
    why not
    EEE R2343 0 10 0 10
    EEE G3333 0 0 10 0

    Why G3333 should be precedent to R2343?

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    ...

    for that example input data might be like this'

    ('CCC','F1234','2014-03-31',6)
    ('CCC','F1234','2014-04-27',2)
    ('CCC','F1234','2014-10-31',3)
    ('CCC','G1234','2014-03-31',4)
    ('CCC','G1234','2014-04-27',8)
    ('CCC','G1234','2014-10-31',7)
    so expected result set like be

    CCC F1234 6 2 0 3
    CCC G1234 4 8 0 7

    In what condition, is it neccesary to populate previous quarter values to next quarter?

    This might be a new requirement which was not mentioned before.
    Right?

    If any quarter (except first quarter) is not having values means ,we should bring it from previous quarters like below
    consider previous example

    CCC F1234 6 2 0 3
    CCC G1234 4 8 0 7

    the result set is not having Q3 value so we can bring the Q2 value to Q3

    CCC F1234 6 2 2 3
    CCC G1234 4 8 8 7
    If the data was slightly modified like

    ('CCC','F1234','2014-03-31',6)
    /* ('CCC','F1234','2014-04-27',2) */
    ('CCC','F1234','2014-10-31',3)
    ('CCC','G1234','2014-03-31',4)
    ('CCC','G1234','2014-04-27',8)
    ('CCC','G1234','2014-10-31',7)
    so expected result set like be

    CCC F1234 6 0 0 3
    CCC G1234 4 8 0 7

    How to fill the 3rd quarter?

    CCC F1234 6 0 x 3
    CCC G1234 4 8 y 7

  13. #13
    Join Date
    Sep 2011
    Posts
    220
    Thanks,
    CCC F1234 6 0 0 3
    CCC G1234 4 8 0 7

    How to fill the 3rd quarter?

    CCC F1234 6 0 x 3
    CCC G1234 4 8 y 7
    means that,the URN_NAME (F1234) is giving 6 upto 3rd quarter and giving 3 for 4th quarter
    The URN_NAME (G1234) is giving 8 upto 3rd quarter and giving 7 for 4th quarter
    so result set like be

    CCC F1234 6 6 6 3
    CCC G1234 4 8 8 7
    In real time the sum of every single quarter will not exceed 10(in that example Q2 and Q3 is having 14..but in real time should not be)

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please describe the rules to fill quarter(which having no value) more consistently and comprehensively.
    I fel that your descriptions might include some inconsistencies.

    Here are some examples which I thought including inconsistencies.
    (1)
    CCC F1234 6 6 6 3
    CCC G1234 4 8 8 7
    Isn't it conflict with
    "If any quarter (except first quarter) is not having values means ,we should bring it from previous quarters"
    in the following quoted post?

    Why fill 2nd quarter of F1234 of CCC?
    2nd quarter of CCC was having a value 8 in G1234.

    Why not
    CCC F1234 6 0 6 3
    CCC G1234 4 8 8 7
    Quote Originally Posted by Billa007 View Post
    ...
    ...

    If any quarter (except first quarter) is not having values means ,we should bring it from previous quarters ...

    ...
    ...

    (2) consider your another example.
    EEE R2343 2014-05-11 10
    EEE G3333 2014-09-09 10

    EXPECTED RESULT SET

    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 10
    The result before fill data was
    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 0

    So, if filled it(EEE) with same ways as
    CCC F1234 6 6 6 3
    CCC G1234 4 8 8 7

    isn't it like
    EXPECTED RESULT SET

    EEE R2343 0 10 10 10
    EEE G3333 0 0 10 10

  15. #15
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply,

    Please describe the rules to fill quarter(which having no value) more consistently and comprehensively.
    I fel that your descriptions might include some inconsistencies.
    1.for every URN is getting the material from one or more URN_NAME based on quarters.
    2.The quantity should be maximum of 10 % per quarter
    3.In the given data,suppose one URN_NAME is supplying full 10% quantity for one quarter,there is no question to go for another URN_NAME for the same quarter
    Ex1:

    RRR R1222 2014-03-28 10

    IN ABOVE R1222 is supplying full 10% for Q1

    the same example,directly means that the URN_NAME R1222 is going to supply full 10 % for the whole year(why because no other URN_NAME is not available for the RRR for remaining quarters)
    Exp Result set

    RRR R1222 10 10 10 10

    Ex2:

    for one URN is getting sum of 10 % from different URN_NAME
    FFF A1232 2014-03-25 3
    FFF B2222 2014-03-25 3
    FFF D2333 2014-03-25 4

    Exp Result

    FFF A1232 3 3 3 3
    FFF B2222 3 3 3 3
    FFF D2333 4 4 4 4

    Ex3:

    The below example URN GGG is not getting the material for FIRST quarter,it is getting material from SECOND quarter onwards
    the URN_NAME D2222 is supplying full 10% for Q2,and Q3 has different URN_NAME E2223 is supplying full 10 %.
    For Q4 again D2222 is supplying material for FOURTH quarter
    GGG D2222 2014-05-31 10
    GGG E2223 2014-09-30 10
    GGG D2222 2014-12-31 10

    Exp Result set

    GGG D2222 0 10 0 10
    GGG E2223 0 0 10 0

    The result before fill data was
    EEE R2343 0 10 0 0
    EEE G3333 0 0 10 0

    So, if filled it(EEE) with same ways as
    CCC F1234 6 6 6 3
    CCC G1234 4 8 8 7

    isn't it like
    EXPECTED RESULT SET

    EEE R2343 0 10 10 10
    EEE G3333 0 0 10 10
    for EEE is getting full 10% from R2343 for SECOND quarter,for the THIRD quarter another URN_NAME is supplying 10%,so, no other URN_NAME is for Q4,means that the G3333 is going to supply for FOURTH quarter also..

    Please let me know,if any
    Thanks again

Posting Permissions

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