Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

First use of CTEs in Query - asking for comments on how to improve query Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 2:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 6:13 AM
Points: 6, Visits: 191
Hello everyone,

Below is my first attempt at use CTEs in a query and I would to read your comments and see if the query can be improved. The query is used by an SSIS package to fill a table in a Datawarehouse and it runs in about a minute and returns 60k rows. That table is then used as a source for an SSRS report to produce this table :

Period Amount Qty Invoices
============================
A-2 87 000 600 27
A-1 83 000 507 29
YTD A-1 5 000 44 7
YTD A 10 000 110 4

Here is the query and thank you for your help :

DECLARE @Year_A INT
DECLARE @Year_A1 INT
DECLARE @Year_A2 INT

SET @Year_A = YEAR(GETDATE());
SET @Year_A1 = @Year_A - 1;
SET @Year_A2 = @Year_A - 2;

WITH
[YTD_A] AS
(
SELECT
SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [Amount_YTD_A]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE (SalesInvoiceLines.InvoiceDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND
SalesInvoiceLines.InvoiceDate <= GETDATE())
AND SalesInvoiceLines.CompanyAccount = '025'
GROUP BY SalesInvoiceLines.POSAccount, ItemLocal.Brand
),

[YTD_A_1] AS
(
SELECT
SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [Amount_YTD_A_1]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE (SalesInvoiceLines.InvoiceDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()) - 1, 0) AND
SalesInvoiceLines.InvoiceDate <= DATEADD(yy, -1, GETDATE()))
AND SalesInvoiceLines.CompanyAccount = '025'
GROUP BY SalesInvoiceLines.POSAccount, ItemLocal.Brand
),

[A_1] AS
(
SELECT
SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [A_1]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE YEAR(SalesInvoiceLines.InvoiceDate) = @Year_A1
AND SalesInvoiceLines.CompanyAccount = '025'
GROUP BY SalesInvoiceLines.POSAccount, ItemLocal.Brand
),

[A_2] AS
(
SELECT
SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [A_2]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE YEAR(SalesInvoiceLines.InvoiceDate) = @Year_A2
AND SalesInvoiceLines.CompanyAccount = '025'
GROUP BY SalesInvoiceLines.POSAccount, ItemLocal.Brand
)

SELECT
CAST('025' AS NVARCHAR(4)) AS [CompanyAccount]
,'A-2' AS [Period]
,SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [NetAmount]
,SUM(SalesInvoiceLines.TotalQty) AS [TotalQty]
,COUNT(DISTINCT SaleSInvoiceLines.InvoiceCount) AS [InvoiceCount]
,SUM(InvoicesGrossSalesBasePrice) AS [InvoicesGrossSalesBasePrice]
,NULL AS [NetAmountA-1]
,NULL AS [NetAmountA-2]
,NULL AS [NetAmountYTDA-1]
,NULL AS [NetAmountYTDA]
,CASE
WHEN SUM(InvoicesGrossSalesBasePrice) = 0.00 THEN 0
ELSE (SUM(InvoicesGrossSalesBasePrice) - SUM(SalesInvoiceLines.NetAmount)) /
SUM(InvoicesGrossSalesBasePrice)
END AS [Sconto]
,NULL AS [Evol]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE SalesInvoiceLines.CompanyAccount = '025'
AND YEAR(SalesInvoiceLines.InvoiceDate) = @Year_A2
GROUP BY SalesInvoiceLines.POSAccount
,ItemLocal.Brand
UNION ALL
SELECT
CAST('025' AS NVARCHAR(4)) AS [CompanyAccount]
,'A-1' AS [Period]
,SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [NetAmount]
,SUM(SalesInvoiceLines.TotalQty) AS [TotalQty]
,COUNT(DISTINCT SaleSInvoiceLines.InvoiceCount) AS [InvoiceCount]
,SUM(InvoicesGrossSalesBasePrice) AS [InvoicesGrossSalesBasePrice]
,A_1.A_1 AS [NetAmountA-1]
,A_2.A_2 AS [NetAmountA-2]
,NULL AS [NetAmountYTDA-1]
,NULL AS [NetAmountYTDA]
,CASE
WHEN SUM(InvoicesGrossSalesBasePrice) = 0.00 THEN 0
ELSE (SUM(InvoicesGrossSalesBasePrice) - SUM(SalesInvoiceLines.NetAmount)) /
SUM(InvoicesGrossSalesBasePrice)
END AS [Sconto]
,CASE
WHEN A_2.A_2 = 0 THEN 0
ELSE (A_1.A_1 - A_2.A_2) / A_2.A_2
END AS [Evol]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
LEFT OUTER JOIN A_1
ON SalesInvoiceLines.POSAccount = A_1.POSAccount
AND ItemLocal.Brand = A_1.Brand
LEFT OUTER JOIN A_2
ON SalesInvoiceLines.POSAccount = A_2.POSAccount
AND ItemLocal.Brand = A_2.Brand
WHERE SalesInvoiceLines.CompanyAccount = '025'
AND YEAR(SalesInvoiceLines.InvoiceDate) = @Year_A1
GROUP BY SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,A_1.A_1
,A_2.A_2
UNION ALL
SELECT
CAST('025' AS NVARCHAR(4)) AS [CompanyAccount]
,'YTD A-1' AS [Period]
,SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [NetAmount]
,SUM(SalesInvoiceLines.TotalQty) AS [TotalQty]
,COUNT(DISTINCT SaleSInvoiceLines.InvoiceCount) AS [InvoiceCount]
,SUM(InvoicesGrossSalesBasePrice) AS [InvoicesGrossSalesBasePrice]
,NULL AS [NetAmountA-1]
,NULL AS [NetAmountA-2]
,NULL AS [NetAmountYTDA-1]
,NULL AS [NetAmountYTDA]
,CASE
WHEN SUM(InvoicesGrossSalesBasePrice) = 0.00 THEN 0
ELSE (SUM(InvoicesGrossSalesBasePrice) - SUM(SalesInvoiceLines.NetAmount)) /
SUM(InvoicesGrossSalesBasePrice)
END AS [Sconto]
,NULL AS [Evol]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
WHERE SalesInvoiceLines.CompanyAccount = '025'
AND (SalesInvoiceLines.InvoiceDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()) - 1, 0) AND
SalesInvoiceLines.InvoiceDate <= DATEADD(yy, -1, GETDATE()))
GROUP BY SalesInvoiceLines.POSAccount
,ItemLocal.Brand
UNION ALL
SELECT
CAST('025' AS NVARCHAR(4)) AS [CompanyAccount]
,'YTD A' AS [Period]
,SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,SUM(SalesInvoiceLines.NetAmount) AS [NetAmount]
,SUM(SalesInvoiceLines.TotalQty) AS [TotalQty]
,COUNT(DISTINCT SaleSInvoiceLines.InvoiceCount) AS [InvoiceCount]
,SUM(InvoicesGrossSalesBasePrice) AS [InvoicesGrossSalesBasePrice]
,NULL AS [NetAmountA-1]
,NULL AS [NetAmountA-2]
,YTD_A_1.Amount_YTD_A_1 AS [NetAmountYTDA-1]
,YTD_A.Amount_YTD_A AS [NetAmountYTDA]
,CASE
WHEN SUM(InvoicesGrossSalesBasePrice) = 0.00 THEN 0
ELSE (SUM(InvoicesGrossSalesBasePrice) - SUM(SalesInvoiceLines.NetAmount)) /
SUM(InvoicesGrossSalesBasePrice)
END AS [Sconto]
,CASE
WHEN YTD_A_1.Amount_YTD_A_1 = 0 THEN 0
ELSE (YTD_A.Amount_YTD_A - YTD_A_1.Amount_YTD_A_1) / YTD_A_1.Amount_YTD_A_1
END AS [Evol]
FROM SalesInvoiceLines
INNER JOIN ItemLocal
ON SalesInvoiceLines.CompanyAccount = ItemLocal.CompanyAccount
AND SalesInvoiceLines.LocalItemNumber = ItemLocal.ItemNumber
AND ItemLocal.ItemGroup IN ('FG','KITS')
LEFT OUTER JOIN YTD_A
ON SalesInvoiceLines.POSAccount = YTD_A.POSAccount
AND ItemLocal.Brand = YTD_A.Brand
LEFT OUTER JOIN YTD_A_1
ON SalesInvoiceLines.POSAccount = YTD_A_1.POSAccount
AND ItemLocal.Brand = YTD_A_1.Brand
WHERE SalesInvoiceLines.CompanyAccount = '025'
AND (SalesInvoiceLines.InvoiceDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND
SalesInvoiceLines.InvoiceDate <= GETDATE())
GROUP BY SalesInvoiceLines.POSAccount
,ItemLocal.Brand
,YTD_A_1.Amount_YTD_A_1
,YTD_A.Amount_YTD_A

OPTION (MAXDOP 12)


Post #1542420
Posted Tuesday, February 18, 2014 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
Your query is quite complex (or at least long). I have no idea why do you have all those UNION ALL if you're always querying the same tables (maybe you need it maybe not) and I have nothing to test with.
If you provide DDL, sample data and expected results based on the sample data you might get better help. For information on how to do it, read the article on my signature.
That said, I believe that you can change your CTEs into one like this:
SELECT S.POSAccount,
I.Brand,
SUM(CASE WHEN S.InvoiceDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
AND S.InvoiceDate <= GETDATE()
THEN S.NetAmount END) AS [Amount_YTD_A],
SUM(CASE WHEN S.InvoiceDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
AND S.InvoiceDate <= DATEADD(yy, - 1, GETDATE())
THEN S.NetAmount END) AS [Amount_YTD_A_1],
SUM(CASE WHEN S.InvoiceDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
AND S.InvoiceDate < DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)
THEN S.NetAmount END) AS [A_1],
SUM(CASE WHEN S.InvoiceDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 2, 0)
AND S.InvoiceDate < DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 1, 0)
THEN S.NetAmount END) AS [A_2]
FROM SalesInvoiceLines S
INNER JOIN ItemLocal I ON S.CompanyAccount = I.CompanyAccount
AND S.LocalItemNumber = I.ItemNumber
AND I.ItemGroup IN ('FG','KITS')
WHERE S.CompanyAccount = '025'
AND S.InvoiceDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()) - 2, 0)
AND S.InvoiceDate <= getdate()
GROUP BY S.POSAccount,
I.Brand

Also, avoid using functions on columns used in WHERE clauses. e.g. YEAR(SalesInvoiceLines.InvoiceDate)



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse