Let me preface this with the admission that I am not a database or SQL expert by any stretch. I know basic queries and have done some slightly more involved queries, but this is beyond my ken.
Background
My company uses JD Edwards Enterprise One for their ERP application. The tables below are represented by actual tables in this system. I have paraphrased them below for simplicity. The back-end database is DB2 running on an AS/400 platform.
Table Definitions
Table: Item Ledger (IL)
Columns:
Business Unit
Work Center
Order #
Doc Type
Trans. Date
Qty Completed
Table: W/O Time Transactions (WOT)
Columns:
Business Unit
Work Center
Order #
Hours Type
G/L Date
Hours Worked
Notes:
Sample Data
Note: Apologies for the poor formatting, but there seems to be no way to represent a table nicely in this venue.
Item Ledger (IL)
Business Unit Work Center Order # Doc Type Trans. Date Qty Completed
BU-1 WC-1 1 IC 8/7/20 20
BU-1 WC-1 1 IC 8/7/20 40
BU-1 WC-1 2 IC 8/7/20 10
BU-1 WC-2 3 IC 8/7/20 40
BU-1 WC-2 3 IC 8/7/20 10
BU-2 WC-4 6 IC 8/7/20 10
BU-2 WC-5 5 IC 8/7/20 60
BU-1 WC-1 2 IC 8/6/20 50
BU-1 WC-3 4 IC 8/6/20 30
BU-2 WC-5 5 IC 8/5/20 50
BU-2 WC-5 7 IC 8/1/20 20
BU-2 WC-5 8 IC 7/26/20 30
BU-2 WC-5 8 IC 7/25/20 50
W/O Time Transactions (WOT)
Business Unit Work Center Order # Hours Type G/L Date Hours Worked
BU-2 WC-4 6 3 8/8/20 7.4
BU-1 WC-1 1 3 8/7/20 3.92
BU-1 WC-2 3 3 8/7/20 8
BU-1 WC-2 3 3 8/7/20 7
BU-1 WC-3 4 3 8/7/20 6.2
BU-2 WC-4 6 3 8/7/20 1.1
BU-1 WC-1 2 3 8/6/20 1.57
BU-2 WC-5 7 3 8/1/20 3.9
BU-2 WC-5 8 3 7/25/20 11.3
Note: No entries for order # 5 in the time transactions table.
Goals
Goal 1:
We are trying to get hours worked and quantities completed, grouped by business unit (e.g. BU-1) and work center (e.g. WC-1), totaled by the week (future: add other time ranges like by the month).
Important things to note are that there may be multiple completions on a given day in the item ledger, and there may be a much smaller (sometimes just one) number of entries in the time transactions for hours worked that correspond to those completions. There are also often situations where the hours worked entry may not be posted until the next day (this is a 24x7 operation with shifts), in which case the related events (completion and hours logging for a given order) are on separate days.
If we were to meld together the data from these two tables, where order number is the common tie:
Note: I assembled this manually, so there could be errors.
Business Unit Work Center Order # Date Hours Worked Qty Completed
BU-2 WC-4 6 8/8/20 7.4 null
BU-1 WC-1 1 8/7/20 3.92 60
BU-1 WC-1 2 8/7/20 null 10
BU-1 WC-2 3 8/7/20 15 50
BU-1 WC-3 4 8/7/20 6.2 null
BU-2 WC-4 6 8/7/20 1.1 10
BU-2 WC-5 5 8/7/20 null 60
BU-1 WC-1 2 8/6/20 1.57 50
BU-1 WC-3 4 8/6/20 null 30
BU-2 WC-5 5 8/5/20 null 50
BU-2 WC-5 7 8/1/20 3.9 20
BU-2 WC-5 8 7/26/20 null 30
BU-2 WC-5 8 7/25/20 11.3 50
Now, grouped by business unit, work center and totaled by the week:
Week Business Unit Work Center Hours Worked Qty Completed
8/2/20 – 8/8/20
BU-2 WC-4 8.5 10
BU-1 WC-1 5.49 120
BU-1 WC-2 15 50
BU-1 WC-3 6.2 30
BU-2 WC-5 0 50
7/26/20 – 8/1/20
BU-2 WC-5 3.9 50
7/19/20 – 7/25/20
BU-2 WC-5 11.3 50
I do not know the proper SQL query to achieve this result. I thought that this would require a full outer join, but everything I have tried seems to result in some sort of Cartesian product for a result set, and this is just the first step of combining the data from the two tables together, never mind the final aggregation and grouping.
Any help and suggestions on how to write a query (or maybe multiple queries?) to achieve this type of result would be very much appreciated.
Update (8/14/20):
I have tried to implement a query based on https://stackoverflow.com/users/10418264/mark-barinstein (Mark Barinstein) suggestions. I've analyzed the suggested query and I think I understand it for the most part.
I'm struggling with getting the JD Edwards Julian date into a form that can be used to calculate the week of the year. Here is may latest attempt. Note that the actual table and column names are in use here.
SELECT
COALESCE(ILG.Week, WOG.Week) AS WEEK,
COALESCE(ILG.BU, WOG.BU) AS BU,
COALESCE(ILG.WC, WOG.WC) AS WC,
COALESCE(WOG.Hours, 0) AS HOURS,
COALESCE(ILG.Qty, 0) AS QTY
FROM
(
SELECT
F31122.WTMMCU AS BU,
F31122.WTMCU AS WC,
WEEK(DATE(CAST(1900 + F31122.WTDGL/1000 AS CHAR(4)) || '-01-01') + (MOD(F31122.WTDGL, 1000) - 1) DAYS) AS Week,
SUM(F31122.WTHRW) AS Hours
FROM PROD2DTA.F31122 AS F31122
WHERE
(F31122.WTTYR IN ('3')) AND
(F31122.WTDGL BETWEEN 120200 AND 120201)
GROUP BY
F31122.WTMMCU,
F31122.WTMCU
) WOG
FULL JOIN
(
SELECT
F4111.ILMCU AS BU,
F4111.ILLOCN AS WC,
WEEK(DATE(CAST(1900 + F4111.ILTRDJ/1000 AS CHAR(4)) || '-01-01') + (MOD(F4111.ILTRDJ, 1000) - 1) DAYS) AS Week,
SUM(F4111.ILTRQT) AS Qty
FROM PROD2DTA.F4111 AS F4111
WHERE
(F4111.ILDCT IN ('IC')) AND
(F4111.ILTRDJ BETWEEN 120200 AND 120201)
GROUP BY
F4111.ILMCU,
F4111.ILLOCN
) ILG
ON ILG.BU = WOG.BU AND ILG.WC = WOG.WC AND ILG.WEEK = WOG.WEEK
ORDER BY 1 DESC, 2, 3
Is there a way to simplify the logic that converts the JDE Julian date to a week number? I understand what's being done, but wish I could break it out as a function and call it.
I'm still getting errors though:
[SQL0122] Column WTDGL or expression in SELECT list not valid. (JdbcException)
Different question: Can the column "alias" (e.g. BU instead of F4111.ILMCU) be used in the GROUP BY clause? It would make it much more readable.
Try this:
SELECT
COALESCE(ILG.WEEK, WOG.WEEK) AS WEEK
, COALESCE(ILG.BU, WOG.BU) AS BU
, COALESCE(ILG.WC, WOG.WC) AS WC
, COALESCE(WOG.Hours, 0) AS HOURS
, COALESCE(ILG.Qty, 0) AS QTY
FROM
(
SELECT BU, WC, WEEK(TO_DATE(GL_Date, 'MM/DD/YY')) AS Week, SUM(Hours) AS Hours
FROM WO
GROUP BY BU, WC, WEEK(TO_DATE(GL_Date, 'MM/DD/YY'))
) WOG
FULL JOIN
(
SELECT
BU, WC
, WEEK(TO_DATE(Trans_Date, 'MM/DD/YY')) AS Week
, SUM(Qty) AS Qty
FROM IL
GROUP BY BU, WC, WEEK(TO_DATE(Trans_Date, 'MM/DD/YY'))
) ILG ON ILG.BU = WOG.BU AND ILG.WC = WOG.WC AND ILG.WEEK = WOG.WEEK
ORDER BY 1 DESC, 2, 3
db<>fiddle link to test it.