SQL Server - Multiple Left Join

[stanl]

I am putting together a script which queries SQL Server for workorders on a given date, then calculates cost of installed equipment and two type of credit/payments depending on if parts were swapped, installed new, or installed as re-manufactured. This data goes to Excel and gets formatted 'purty'.

I tried the SQL syntax below, but get duplicate rows of workorders {which I shouldn't}, so for now I broke the routine into a single query of workorders which I loop through performing sum queries on the other tables as I fill in the Excel rows. Obviously, the single query and then copyfromrecordset() would be much faster, so I'm open to suggestions. The sample below uses fully-qualified table/field names which I will alias if I get it working right. Stan


SELECT dbo.WOR.cWorkOrder, Sum(dbo.Installs.cost) AS Installed, Sum(dbo.cfrecon.pmt) AS Credit_Memo, Sum(dbo.payments.nPayment) AS EPP_CM
FROM dbo.WOR
LEFT JOIN dbo.Installs ON dbo.Installs.cWorkOrder = dbo.WOR.cWorkOrder
LEFT JOIN dbo.cfrecon ON dbo.cfrecon.cWorkOrder = dbo.WOR.cWorkOrder
LEFT JOIN dbo.payments ON dbo.payments.cWorkOrder = dbo.WOR.cWorkOrder
GROUP BY dbo.WOR.cWorkOrder, dbo.payments.PmtMethod, dbo.WOR.Completed
HAVING (((dbo.payments.PmtMethod)='CM') AND ((dbo.WOR.Completed)>='9/1/2008' And (dbo.WOR.Completed)<'9/2/2008'));

[Mavyak]

What about using 4 queries (3 unioned together)?

Select x...
From (

Select ...
From dbo.WOR
Inner Join dbo.Installs

union all

Select ...
From dbo.WOR
Inner Join dbo.cfrecon

union all

Select ...
From dbo.WOR
Inner Join dbo.payments) X

Group By x...
Having x...

[stanl]

doesn't union all create duplicates? Also, I think the problem is that handling a null result for a specific summed field.:dunno Stan

[Mavyak]

Does the same workorder exist in all three tables? The grouping and summation would occur in the outer query. That should prevent any duplicates.

[stanl]

Does the same workorder exist in all three tables? The grouping and summation would occur in the outer query. That should prevent any duplicates.

Not necessarily; and that is why I think the LEFT JOINS failed. I originally thought about using UNION but got a little confused - {as I read...}

UNION - by itself should produce no duplicates

UNION ALL - should

however, in SQL Server and MySQL UNION ALL supposedly eliminates duplicates. UNION expects the same number of fields, the same name and the same data type.

To describe the situation a little more:

Consider Workorder 101: equipment a,b,and c installed - b is swapped for an earlier version of b. So in the installed cost table 3 entries are made for that workorder, The credit payments table has 2 entries for a and c, while the credit memos will have either an entry for b or the old b after old b is returned.

at this point all three related tables have entries for the workorder

Workorder 102: Only a is installed, no credit memo record created

Workorder 103: c is swapped out for a defective c - only a credit memo is created.

The reason two distinct credit entries are kept is to distinguish between an actual payment/as credit and a credit against you account used to purchase new equipment for installs.

{and this doesn't even cover partial credits}.

Right now, I'm only looking at 500-600 workorders per day, so the looping code I wrote does not create a lot of overhead.

Stan

[Mavyak]

SELECT
X.cWorkorder AS Work_Order_Number,
SUM(X.Installed) AS Installed,
SUM(X.Credit_Memo) AS Credit_Memo,
SUM(X.EPP_CM) AS EPP_CM
FROM (
SELECT
dbo.WOR.cWorkOrder,
ISNULL(dbo.Installs.cost, 0) AS Installed,
0 AS Credit_Memo,
0 AS EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.Installs ON dbo.Installs.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008'

UNION ALL

SELECT
dbo.WOR.cWorkOrder,
0 AS Installed,
ISNULL(dbo.cfrecon.pmt, 0) As Credit_Memo,
0 AS EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.cfrecon ON dbo.cfrecon.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008'

UNION ALL

SELECT
dbo.WOR.cWorkOrder,
0 AS Installed,
0 AS Credit_Memo,
ISNULL(dbo.payments.nPayment, 0) As EPP_CM
FROM
dbo.WOR
INNER JOIN dbo.payments ON dbo.payments.cWorkOrder = dbo.WOR.cWorkOrder
WHERE
dbo.payments.PmtMethod = 'CM'
AND
dbo.WOR.Completed >= '9/1/2008'
AND
dbo.WOR.Completed < '9/2/2008') X
GROUP BY
X.cWorkOrder
ORDER BY
1, 2, 3, 4

[stanl]

Interesting.... Thank you... hope to test it tomorrow:bow: