• 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