Running Total with Reset – SQL Server 2012


The trick part is to create a group based the resetflag column with a SUM Over first.

CREATE TABLE [dbo].[test1](
	[rn] [int] NULL,
	[ResetFlag] [int] NULL,
	[Value] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (1, 1, 100)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (2, 0, 4)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (3, 0, 8)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (4, 1, 105)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (5, 0, 21)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (6, 0, 5)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (7, 1, 130)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (8, 0, 10)
GO
; with mycte as
(select *, sum(resetflag) Over(order by rn) grn from test1)
SELECT rn,ResetFlag,Value,SUM(value)
                            OVER(
                              partition BY grn
                              ORDER BY rn) AS groupRunningTotal
FROM   mycte 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d3f07e58-1cf7-4975-9c4e-616208fac67a

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