SQLTeam.com | Weblogs | Forums

YTD and monthly totals


#1

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


#2

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

#3

JamesK:
Thanks again for your help. I think your suggestion does the trick. Very much appreciate your time and quick reply too


#4

Glad to help.

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.


#5

Yes, got that, thanks. And many thanks again for your help