Running Total for Two Columns


  
  
 USE [DB2]
GO
/****** Object:  Table [dbo].[test]    Script Date: 12/27/2016 10:11:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
	[FAMID] [int] NULL,
	[MEMID] [int] NULL,
	[CLM] [varchar](10) NULL,
	[DT] [date] NULL,
	[OOP] [int] NULL,
	[MEMOOP] [int] NULL,
	[FAMOOP] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([FAMID], [MEMID], [CLM], [DT], [OOP], [MEMOOP], [FAMOOP]) 
VALUES (1234, 1, N'abc', CAST(N'2016-01-01' AS Date), 15, 15, 15),
(1234, 1, N'def', CAST(N'2016-01-02' AS Date), 15, 30, 30),
(1234, 1, N'ghi', CAST(N'2016-01-03' AS Date), 15, 45, 45),
(1234, 2, N'klm', CAST(N'2016-01-04' AS Date), 15, 15, 60),
(1234, 2, N'nop', CAST(N'2016-01-05' AS Date), 15, 30, 75),
(1234, 2, N'qrs', CAST(N'2016-01-06' AS Date), 15, 45, 90),
(1234, 3, N'tuv', CAST(N'2016-01-07' AS Date), 15, 15, 105),
(1234, 3, N'wxy', CAST(N'2016-01-08' AS Date), 15, 30, 120),
(1234, 3, N'z12', CAST(N'2016-01-09' AS Date), 15, 45, 135)




Set statistics io on
Set statistics time on

;with mycte as (select *
,row_number() Over(Partition by MEMID Order by DT) rn 
, row_number() Over( Order by DT) rn1 
from test
)


,mycte1 AS
(
    SELECT FAMID,MEMID,CLM,DT,	OOP, OOP as runningtotal1,   OOP as runningtotal2
	, rn1
	-- ,rn  
	FROM mycte
    WHERE rn1 = 1  
    UNION ALL
    SELECT  m.FAMID,m.MEMID,m.CLM,m.DT,	m1.OOP, m1.runningtotal1+ m.OOP,Case when m.MEMID=m1.MEMID 
	then m1.runningtotal2+ m.OOP else m1.OOP end 	
	,m.rn1
	--, m.rn	  
      FROM mycte1 m1 INNER JOIN mycte m ON m.rn1 = m1.rn1 + 1 	
 
)

 

Select FAMID,	MEMID,	CLM, DT,OOP
, runningtotal1, runningtotal2  
from mycte1
Order by FAMID,	MEMID,DT

option(maxrecursion 0)


--SQL Server 2012 SUM(..) Over 
Select FAMID,	MEMID,	CLM, DT,	OOP
, Sum(OOP) Over(Partition by FAMID Order by DT ) runningtotal1
, Sum(OOP) Over(Partition by FAMID,MEMID Order by DT) as runningtotal2  
from test
Order by FAMID,	MEMID,DT
 

Set statistics io off
Set statistics time off


 
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