I am trying to get both YTD totals and monthly totals from the same fields in one select statement and am having troubles. I need the monthly and YTD totals for both Order_Amnt and Commission_Amnt. Hope this makes sense. Thanks for any help anyone can provide.
Select Product_ID as ProductName,
Sum(Order_Amnt) As Amt_Ordered_Month,
Sum(Commission_Amnt) As Commission_Month
--need the same sums as above for YTD for Order_Amnt and Commission_Amnt
From Orders o
join SalesReps s on s.SalesRepNo=o.Order_SalesRep_No
join wo_Sales_Product_Families s on s.Product_Family_NO=o.Ord_Product_Family
Where o.Ord_Prd_Type = 'ABC')
and o.Order_Commission_Date Between '04/1/2016' and '04/03/2016'
--need an additional where clause here to get the YTD, something like this:
[and o.Order_Commission_Date Between '01/1/2016' and '04/03/2016']
Group By Product_ID , Order_Product_Type
Order By Product_ID , Order_Product_Type
Do the filtering in the SUM expression like shown below. You can add a where clause that filters all the data for the year, in which case, the Amt_Ordered_Year and Commission_Year can simply be the SUM rather than SUM with the CASE expression within them.
SELECT Product_ID AS ProductName ,
SUM(CASE
WHEN Order_Commission_Date >= DATEADD(MONTH,0,DATEDIFF(MONTH,0,GETDATE())) THEN Order_Amnt
ELSE 0
END) AS Amt_Ordered_Month ,
SUM(CASE
WHEN Order_Commission_Date >= DATEADD(MONTH,0,DATEDIFF(MONTH,0,GETDATE())) THEN Commission_Amnt
ELSE 0
END) AS Commission_Month ,
SUM(CASE
WHEN Order_Commission_Date >= DATEADD(YEAR,0,DATEDIFF(YEAR,0,GETDATE())) THEN Order_Amnt
ELSE 0
END) AS Amt_Ordered_Year ,
SUM(CASE
WHEN Order_Commission_Date >= DATEADD(YEAR,0,DATEDIFF(YEAR,0,GETDATE())) THEN Commission_Amnt
ELSE 0
END) AS Commission_Year
FROM
In my previous posting, I had an error - I was using DATEADD(DAY instead of DATEADD(MONTH. Fixed it now, but I guess you figured that out even before I saw it.