Running Total For a Group


 

create table test ([Day] int,[Car] int,X int, Measure int, Result int default 0)
insert into test ([Day],Car,X,Measure) values(1,11,1,2),(2,11,1,3)
,(3,11,0,4),(4,11,1,5),(5,11,1,2)
,(6,11,0,1),(7,11,1,2),(8,11,1,2)

-- --SQL Server 2012 or 2014
--Option 1
 ;with mycte as (
 SELECT [Day],Car,X,Measure, 
 Case WHEN X=0 Or lag(X, 1) Over(ORDER BY [Day])=0 
 Then Measure*X Else ISNULL(Measure+lag(Measure, 1) Over(ORDER BY [Day]) ,Measure)
 END Result 
FROM test)

Select [Day],Car,X,Measure, Result 
from mycte


---- Option 2
; with mycte as
(
select *
, row_number() Over(Order by [Day])-row_number() Over(partition BY [Car] , X Order by [Day]) grn 
from test)

SELECT [Day],X,Measure,grn, SUM(Measure*X)
OVER(partition BY [Car] ,grn ORDER BY [Day]) AS Result 
 FROM mycte 
-- Order By [Day]


 -- Option 3
; with mycte as
(
select *, sum(X-1) Over(Order by [Day]) grn from test)
select [Day],[Car],X ,Measure, SUM(Measure*X)
OVER(partition BY [Car] ,grn ORDER BY [Day]) AS Result from mycte
Order By [Day]
 
-- Option 4
--Based on Visakh16's cross apply

SELECT t.*,CASE WHEN X = 0 THEN 0 ELSE SUM(Measure) Over(Partition By aDay Order By [Day] ) End AS Result
FROM test t
OUTER APPLY (SELECT max(Day) AS aDay
FROM test
WHERE Day < t.Day
AND X = 0
AND Car = t.Car) t1



--Option from Visakh16
--https://social.msdn.microsoft.com/Forums/en-US/a5a0f339-199b-496e-9d2c-c202acf6c94a/running-count-in-sequence?forum=transactsql
SELECT t.*,
CASE WHEN X = 0 THEN 0 ELSE t2.Result END AS Result
FROM test t
OUTER APPLY (SELECT MAX(Day) AS Prev
FROM test
WHERE Day < t.Day
AND X = 0
AND Car = t.Car
)t1
CROSS APPLY (
SELECT SUM(Measure) AS Result
FROM test
WHERE Day <= t.Day
AND Car = t.Car
AND X = 1
AND (Day > Prev
OR Prev IS NULL)
)t2




drop table test
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s