February 18, 2014 at 2:03 am
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)
February 18, 2014 at 8:55 am
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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply