# Thread: rows clubbed based on Quarters

1. Registered User
Join Date
Sep 2011
Posts
220

## Unanswered: rows clubbed based on Quarters

Hi all,

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. Registered User
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

3. Registered User
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 04:11.

4. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by Billa007
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by Billa007
...

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?

by using considerable various sample data including some exceptional cases(and expected results from the data),

8. Registered User
Join Date
Sep 2011
Posts
220
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by Billa007
...

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. Registered User
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. Registered User
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
Originally Posted by Billa007
...
...

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

...
...

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. Registered User
Join Date
Sep 2011
Posts
220

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..