stanl
09-13-2008, 10:59 AM
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'));
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'));