August 26, 2016 at 5:01 am
I have a requirement to get the running total with different categories
Eg:
Date Region Sub region Value
01/08/2015 USA Texas 10000
01/08/2015 USA Atlanta 2000
01/08/2015 USA Texas 3000
02/08/2015 Canada XYZ 5000
02/08/2015 Canada ABC 2000
02/08/2015 Canada XYZ 1000
Is there any way to get the running total for the above result data set. I.e Sum the previous day values with the current day and we should also consider the other fields like Bookingdate,region and subregion. If i slice on any of these parameters the running total should calculate accordingly.
Please provide any inputs on the same
-Prakash
August 26, 2016 at 5:24 am
Prakash
What question are you asking here - how to do running totals, or how to change how they're partitioned at run time?
The answer to the first question is to use the window functions, something like this (from memory, so forgive any syntax errors):
SUM(Value) OVER (PARTITION BY Region ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
The answer to the second question is that you can't do that, as far as I know, without using dynamic SQL.
John
August 26, 2016 at 8:08 am
If you are talking about simply filtering a resultset, then the code below will work without resorting to dynamic SQL.
Declare @Region varchar(10), @SubRegion varchar(10)
Set @region = 'USA'
Set @SubRegion = 'XYZ'
;with cte as (select * from #Input where Region = @Region)
select *, SUM(ValueX) OVER (Order by DateX ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from cte
You could add SubRegion or anything else to the where clause. However, the more flexible you make your where clause the more likely it is that it will scan the entire input - which can become a performance issue. If that is a concern, come back with more questions and we can discuss it in more detail.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2016 at 8:15 am
If by "slice", you mean filter, windowed functions are calculated after the WHERE clause, so the running total reflects any filters applied in the WHERE clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply