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

  • 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)

  • 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

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

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