SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


First use of CTEs in Query - asking for comments on how to improve query


First use of CTEs in Query - asking for comments on how to improve query

Author
Message
e.vercruysse
e.vercruysse
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 276
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)


Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41742 Visits: 19815
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search