SQL query resouce time

Question

SQL query resouce time

andy.woodward

SSC Veteran

Points: 205
More actions
June 1, 2011 at 2:26 am

#238509

Hi all,
I have a T-SQL query that is taking too long to process. It is probably the way that it is written and I would like your help please.
I have a database that I can't change the design so I have to work with what I have. There are 3 tables I am querying. The database holds records of printing transactions from all users.
Table 1 is called tblTransaction (holds the transaction data)
Table 2 is called tblItem (holds the different types of prints)
Table 3 is called tblLedger(holds the cost centres)
The main problem is the database doesn't let you put users into departments and my result needs to be split into departments. Also it doesn't hold the prices of the print items.
The end result needs to show how much each department has spent on printing in the month of May 2011. The way I have split it up into departments is by naming the cost centres beginning with the same text e.g. CR-Service Development Team (this cost centre belongs to Corporate Resources).
There are 8 print types:
A4 B&W - Print (£0.00772 per print)
A4 Back B&W - Print (£0.0037 per print)
A4 Colour - Print (£0.0403 per print)
A4 Back Colour - Print (£0.036 per print)
A3 B&W - Print (£0.0135 per print)
A3 Back B&W - Print (£0.0037 per print)
A3 Colour - Print (£0.0458 per print)
A3 Back Colour - Print (£0.036 per print)
The query I have is very long but does work but takes up a lot of resources and causes deadlock sometimes. Is there any other way I could simplify it.
Here is the query:
SELECT [A&C].[A&C], CR.CR, CEX.CEX, CYPS.CYPS, HTWM.HTWM
FROM
(SELECT q1.[A&C Cost]+ q2.[A&C Cost]+ q3.[A&C Cost]+q4.[A&C Cost]+q5.[A&C Cost]+q6.[A&C Cost]+q7.[A&C Cost]+q8.[A&C Cost]AS "A&C"
FROM
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.00772 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A4 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q1
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A4 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q2
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.04002 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A4 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q3
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A4 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q4
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.0135 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A3 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q5
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A3 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q6
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.0458 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A3 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q7
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "A&C Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'A&C%' AND T2.LongName = 'A3 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q8
ON q1.[A&C Cost]>0 OR q1.[A&C Cost]<0 OR q1.[A&C Cost]=0)AS "A&C"
JOIN
(SELECT q1.[CR Cost]+ q2.[CR Cost]+ q3.[CR Cost]+q4.[CR Cost]+q5.[CR Cost]+q6.[CR Cost]+q7.[CR Cost]+q8.[CR Cost]AS "CR"
FROM
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.00772 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A4 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q1
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A4 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q2
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.04002 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A4 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q3
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A4 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q4
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.0135 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A3 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q5
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A3 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q6
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.0458 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A3 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q7
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CR Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CR%' AND T2.LongName = 'A3 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q8
ON q1.[CR Cost]>0 OR q1.[CR Cost]<0 OR q1.[CR Cost]=0)AS "CR"
ON CR.CR<0 OR CR.CR>0 OR CR.CR=0
JOIN
(SELECT q1.[CEX Cost]+ q2.[CEX Cost]+ q3.[CEX Cost]+q4.[CEX Cost]+q5.[CEX Cost]+q6.[CEX Cost]+q7.[CEX Cost]+q8.[CEX Cost]AS "CEX"
FROM
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.00772 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A4 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q1
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A4 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q2
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.04002 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A4 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q3
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A4 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q4
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.0135 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A3 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q5
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A3 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q6
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.0458 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A3 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q7
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CEX Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CEX%' AND T2.LongName = 'A3 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q8
ON q1.[CEX Cost]>0 OR q1.[CEX Cost]<0 OR q1.[CEX Cost]=0)AS "CEX"
ON CR.CR<0 OR CR.CR>0 OR CR.CR=0
JOIN
(SELECT q1.[CYPS Cost]+ q2.[CYPS Cost]+ q3.[CYPS Cost]+q4.[CYPS Cost]+q5.[CYPS Cost]+q6.[CYPS Cost]+q7.[CYPS Cost]+q8.[CYPS Cost]AS "CYPS"
FROM
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.00772 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A4 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q1
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A4 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q2
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.04002 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A4 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q3
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A4 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q4
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.0135 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A3 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q5
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A3 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q6
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.0458 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A3 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q7
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "CYPS Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'CYPS%' AND T2.LongName = 'A3 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q8
ON q1.[CYPS Cost]>0 OR q1.[CYPS Cost]<0 OR q1.[CYPS Cost]=0)AS "CYPS"
ON CR.CR<0 OR CR.CR>0 OR CR.CR=0
JOIN
(SELECT q1.[HTWM Cost]+ q2.[HTWM Cost]+ q3.[HTWM Cost]+q4.[HTWM Cost]+q5.[HTWM Cost]+q6.[HTWM Cost]+q7.[HTWM Cost]+q8.[HTWM Cost]AS "HTWM"
FROM
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.00772 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A4 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q1
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A4 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q2
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.04002 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A4 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q3
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A4 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q4
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.0135 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A3 B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q5
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.0037 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A3 Back B&W - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q6
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.0458 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A3 Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q7
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0
JOIN
(SELECT COALESCE(SUM(ACA4BW.TotalQuantity),0) AS "HTWM Cost"
FROM
(select
SUM(T1.Quantity)*0.036 AS TotalQuantity, T2.LongName
FROM tblTransaction T1
JOIN tblItem T2 ON T1.ItemID = T2.ItemID
JOIN tblLedger T3 ON T1.LedgerID = T3.LedgerID
WHERE (TransactionDateTime BETWEEN '2011-05-01' AND '2011-05-31')
AND T3.ShortName LIKE 'HTWM%' AND T2.LongName = 'A3 Back Colour - Print'
GROUP BY T2.Longname, T3.LongName)AS ACA4BW)q8
ON q1.[HTWM Cost]>0 OR q1.[HTWM Cost]<0 OR q1.[HTWM Cost]=0)AS "HTWM"
ON CR.CR<0 OR CR.CR>0 OR CR.CR=0
The result looks like this:
A&C,CR,CEX,CYPS,HTWM
1629.06,2126.356,1584.28,2586.642,1856.69
Hope you can help
Thanks
Andy

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post execution plan, table definition, index definitions. See http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass