Good afternoon,
I'm trying to create a daily sales report summary joining a few different files and summarizing based on varying factors.
The report will end up as follows:
Category On Hold Back Orders Late Current Month Next Month Future Months Total
LOC 24.75 50.01 15.00 45.00 25.25 28.03 188.04
MAR 16.15 21.85 31.20 18.00 25.00 34.05 146.25
....
with various accounting classes. I'm pulling the data from an AS400, IBM I server using DB2 SQL.
I can summarize some of the categories independently, but am having trouble joining them together. For example On Hold:
SELECT x.CDA0CD, SUM(x.CDDUVA) AS "On Hold"
FROM (
SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
LINES.CDDCCD, /* ORDER TYPE */
LINES.CDAITX, /* ITEM NUMBER */
LINES.CDGLCD, /* ITEM CLASS */
LINES.CDA0CD, /* IAC */
LINES.CDDUVA, /* LC - NET SALES AMOUNT */
HOLDS.CKCZCD, /* HOLD CODE */
HOLDS.CKACP8, /* RELEASE TYPE */
LINES.CDZ902 /* TOTAL BACKORDERED QTY */
FROM MBCDREP LINES,
MBCKREP HOLDS
WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS ON HOLD */
LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */
LINES.CDZ902 = 0 AND /* NO BACKORDERS */
HOLDS.CKACP8 != '1') x /* HOLD NOT RELEASED */
GROUP BY x.CDA0CD
WITH ROLLUP
Gives my result:
Item accounting "On Hold"
class
LAY 8,357.290
LOC 909.570
MAR 2,666.340
MCT 4,933.970
SAF 378.000
STM 876.550
STN 4,739.800
TAG 2,709.280
TAP 6,670.930
VIS 1,885.100-
- 30,356.630
And for Back Orders:
SELECT y.CDA0CD, SUM(y.CDDUVA) AS "Back Orders"
FROM (
SELECT CDCVNB, /* QUOTE/ORDER NO */
CDDCCD, /* ORDER TYPE */
CDAITX, /* ITEM NUMBER */
CDGLCD, /* ITEM CLASS */
CDA0CD, /* IAC */
CDDUVA, /* LC - NET SALES AMOUNT */
CDZ902 /* TOTAL BACKORDERED QTY */
FROM MBCDREP
WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */
CDZ902 > 0) y /* BACKORDERS QTY GREATER THAN ZERO */
GROUP BY y.CDA0CD
WITH ROLLUP
With results:
Item accounting "Back Orders"
class
LOC 24.750
MAR 917.580
MCT 67,366.110
OTH .000
STM 10.580
TAG 3,225.440
TAP 106.310
VIS 16,675.380
- 88,326.150
I want to join those together, plus other summaries based on the above goal. I may need a full outer join, which isn't supported in DB2, so I tried a convoluted Left Outer Join and a Right Exceptions, to no avail (sort of following this suggestion: https://www.mcpressonline.com/analytics-cognitive/db2/techtip-full-outer-joins-on-db2-for-i5os):
SELECT x.CDA0CD, x.CDDUVA AS On_Hold
FROM (
SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
LINES.CDDCCD, /* ORDER TYPE */
LINES.CDAITX, /* ITEM NUMBER */
LINES.CDGLCD, /* ITEM CLASS */
LINES.CDA0CD, /* IAC */
LINES.CDDUVA, /* LC - NET SALES AMOUNT */
HOLDS.CKCZCD, /* HOLD CODE */
HOLDS.CKACP8, /* RELEASE TYPE */
LINES.CDZ902 /* TOTAL BACKORDERED QTY */
FROM MBCDREP LINES,
MBCKREP HOLDS
WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS
ON HOLD */
LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */
LINES.CDZ902 = 0 AND /* NO BACKORDERS */
HOLDS.CKACP8 != '1') x /* HOLD NOT RELEASED */
LEFT OUTER JOIN
(SELECT *
FROM MBCDREP
WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */
CDZ902 > 0) y /* BACKORDERS QTY GREATER THAN ZERO */
ON x.CDA0CD = y.CDA0CD
UNION
(SELECT yy.CDA0CD,
yy.CDDUVA AS Back_Orders
FROM (
SELECT CDCVNB, /* QUOTE/ORDER NO */
CDDCCD, /* ORDER TYPE */
CDAITX, /* ITEM NUMBER */
CDGLCD, /* ITEM CLASS */
CDA0CD, /* IAC */
CDDUVA, /* LC - NET SALES AMOUNT */
CDZ902 /* TOTAL BACKORDERED QTY */
FROM MBCDREP
WHERE CDDCCD = 1 AND /* ORDER TYPE: ORDER */
CDZ902 > 0) yy
EXCEPTION JOIN
(SELECT xx.CDA0CD, xx.CDDUVA AS On_Hold
FROM (
SELECT LINES.CDCVNB, /* QUOTE/ORDER NO */
LINES.CDDCCD, /* ORDER TYPE */
LINES.CDAITX, /* ITEM NUMBER */
LINES.CDGLCD, /* ITEM CLASS */
LINES.CDA0CD, /* IAC */
LINES.CDDUVA, /* LC - NET SALES AMOUNT */
HOLDS.CKCZCD, /* HOLD CODE */
HOLDS.CKACP8, /* RELEASE TYPE */
LINES.CDZ902 /* TOTAL BACKORDERED QTY */
FROM MBCDREP LINES,
MBCKREP HOLDS
WHERE LINES.CDCVNB = HOLDS.CKCVNB AND /* SELECT ONLY LINES FROM ORDERS
ON HOLD */
LINES.CDDCCD = 1 AND /* ORDER TYPE: ORDER */
LINES.CDZ902 = 0 AND /* NO BACKORDERS */
HOLDS.CKACP8 != '1') xx) xxx
on yy.CDA0CD = xxx.CDA0CD
)
Which only gives me On hold...
Item accounting ON_HOLD
class
TAP 59.160
LAY 1,668.000
LOC 27.230
STM 83.490
STM 25.120
STM 75.370
LAY 1.500
LAY 18.270
STN 61.580
LAY 23.040
MAR 2.400
LAY 218.680
TAG 523.980
TAG 524.040
LAY 1,819.800
MCT 470.400
TAG 65.930
TAG 33.630
....
Please help! I know there's got to be a simpler way. I'm having date format issues, too, but I'll post that as a separate question, since it's technically a different issue, though I'll end up joining them all together.
Thanks.
Try this
select CDA0CD , sum(holdvalue) , sum(backordervalue)
from
(
/* hold query /
select CDA0CD , SUM(CDDUVA) as holdvalue , 0 as backordervalue
......
/ hold query /
union all
/ backorder query /
select CDA0CD , 0 as holdvalue , SUM(CDDUVA) as backordervalue
......
/ backorder query */
)
group by CDA0CD