Hello All,
I am in need of some assistance with a query.

Based on the query below, I would like to show YTDSales, PYTDSales, YTDProfit, and PYTDProfit for every customer.

I assume that I need a subquery, but my attempts have failed. Through much trial and error, this is as far as I have got:

SELECT
DISTINCT S.CustomerName,
SUM(S.TotSales) AS YTDSales,
SUM(S.PubProfit) AS YTDPubProfit
FROM vInvoiceSummary S INNER JOIN ARCusts ON S.CustAcct=ARCusts.CustAcct INNER JOIN ARSmen S1 ON ARCusts.SmanID=S1.SmanID
WHERE S.InvoiceDate BETWEEN '1/1/' + cast(DATEPART(YYYY,GETDATE()) as varchar(4)) AND GETDATE() AND S1.SalesmanName = 'HOUSE'
GROUP BY S.CustomerName
UNION
SELECT
DISTINCT S.CustomerName,
SUM(S.TotSales) AS PYTDSales,
SUM(S.PubProfit) AS PYTDPubProfit
FROM vInvoiceSummary S INNER JOIN ARCusts ON S.CustAcct=ARCusts.CustAcct INNER JOIN ARSmen S1 ON ARCusts.SmanID=S1.SmanID
WHERE S.InvoiceDate BETWEEN '1/1/' + cast(DATEPART(YYYY,DATEADD(YEAR,-1,GETDATE())) as varchar(4)) AND DATEADD(YEAR,-1,GETDATE()) AND S1.SalesmanName = 'HOUSE'
GROUP BY S.CustomerName

This results in the following:
CustomerName______YTDSales_____YTDProfit
---------------------------------------------
Customer A..............2066.57..........619.28
Customer A..............3482.54..........1061.14
Customer B..............369.46............62.41
Customer C..............0...................-116.43
Customer D..............244.71............107.17
Customer D..............617.15.............41.1

I'm trying to get this:
CustomerName_____YTDSales____PYTDSales____YTDProfi t____PYTDProfit
-----------------------------------------------------------------------
Customer A............2066.57........3482.54..........619.2 8..........1061.14
Customer B............369.46..........62.41.............0.. ...............0
Customer C............0.................-116.43..........0.................0
Customer D............244.71..........617.15...........107. 17..........41.1

(I also need to calc the %change YTD vs PYTD on both sales and proift).


Any help would be greatly appreciated.

Thanks,
Todd