Running Total With Group based on business logic (T-sql sample)


  
declare @t table
(
ID	int,
Patid char(10),
DrugID char(10),
DateVal char(10),
NextDate char(10),
Duration char(10),
qty	char(10),
--FinishDate datetime,
Remaining int
)
insert @t
values
--ob	ID	PatId	DrugID	DateVal	NextDate	Duration	qty	Remaining	cB	lq
(	1	,'Pat2',	'Drug1',	'2012-10-25',	'2013-02-28',	'126  ',	'120',	-6	)	,
(	2	,'Pat2',	'Drug1',	'2013-02-28',	'2013-06-20',	'112  ',	'112',	0	)	,
(	3	,'Pat2',	'Drug1',	'2013-06-20',	'2013-10-08',	'110  ',	'120',	10	)	,
(	4	,'Pat2',	'Drug1',	'2013-10-08',	'2014-01-13',	'97   ',	'120  ',	23	)	,
(	5	,'Pat2',	'Drug1',	'2014-01-13',	'2014-01-28',	'15   ',		NULL,	-15	)	,
(	6	,'Pat2',	'Drug1',	'2014-01-28',	'2014-02-03',	'6    ',	'120  ',	114	)	,
(	7	,'Pat2',	'Drug1',	'2014-02-03',	'2014-02-07',	'4    ',	'14   ',	10	)	,
(	8	,'Pat2',	'Drug1',	'2014-02-07',	'2014-08-12',	'186  ',		NULL,	-186	)	,
(	9	,'Pat2',	'Drug1',	'2014-08-12',	'2014-12-02',	'112  ',	'120  ',	8	)	,
(	10	,'Pat2',	'Drug1',	'2014-12-02',	'2015-03-22',	'110  ',	'120  ',	10	)	,
(	11	,'Pat2',	'Drug1',	'2015-03-22',	'2015-03-26',	'4    ',	'14   ',	10	)	,
(	12	,'Pat2',	'Drug1',	'2015-03-26',	'2015-04-17',	'22   ',	'14   ',	-8	)	,
(	13	,'Pat2',	'Drug1',	'2015-04-17',	 NULL,			NULL,		'120  ',	120	)	,
(	1	,'Pat2',	'Drug2',	'2011-02-02',	'2011-02-04',	'2    ',	'1    ',	-1	)	,
(	2	,'Pat2',	'Drug2',	'2011-02-04',	'2011-06-20',	'136  ',	NULL,	-136	)	,
(	3	,'Pat2',	'Drug2',	'2011-06-20',	'2012-11-19',	'518  ',	NULL,	-518	)	,
(	4	,'Pat2',	'Drug2',	'2012-11-19',	'2012-11-27',	'8    ',	'7    ',	-1	)	,
(	5	,'Pat2',	'Drug2',	'2012-11-27',	'2014-01-10',	'409  ',	'90   ',	-319	)	,
(	6	,'Pat2',	'Drug2',	'2014-01-10',	'2014-01-11',	'1    ',	'14   ',	13	)	,
(	7	,'Pat2',	'Drug2',	'2014-01-11',	'2014-02-03',	'23   ',	'14   ',	-9	)	,
(	8	,'Pat2',	'Drug2',	'2014-02-03',	'2014-02-07',	'4    ',	'14   ',	10	)	,
(	9	,'Pat2',	'Drug2',	'2014-02-07',	'2015-03-21',	'407  ',	NULL,	-407	)	,
(	10	,'Pat2',	'Drug2',	'2015-03-21',	'2015-03-22',	'1    ',	'14   ',	13	)	,
(	11	,'Pat2',	'Drug2',	'2015-03-22',	'2015-03-26',	'4    ',	'14   ',	10	)	,
(	12	,'Pat2',	'Drug2',	'2015-03-26',	 NULL,			NULL,		'14   ',	14	)	,
(	1	,'Pat1',	'Drug3',	'2012-06-27',	'2012-08-08',	'42   ',	'60   ',	18	)	,
(	2	,'Pat1',	'Drug3',	'2012-08-08',	'2012-08-29',	'21   ',	NULL,	-21	)	,
(	3	,'Pat1',	'Drug3',	'2012-08-29',	'2012-09-24',	'26   ',	'30   ',	4	)	,
(	4	,'Pat1',	'Drug3',	'2012-09-24',	'2012-12-02',	'69   ',	'60   ',	-9	)	,
(	5	,'Pat1',	'Drug3',	'2012-12-02',	'2012-12-19',	'17   ',	'30   ',	13	)	,
(	6	,'Pat1',	'Drug3',	'2012-12-19',	'2013-01-30',	'42   ',	'120  ',	78	)	,
(	7	,'Pat1',	'Drug3',	'2013-01-30',	'2013-02-27',	'28   ',	NULL,	-28	)	,
(	8	,'Pat1',	'Drug3',	'2013-02-27',	'2013-04-24',	'56   ',	'30   ',	-26	)	,
(	9	,'Pat1',	'Drug3',	'2013-04-24',	'2013-08-14',	'112  ',	'90   ',	-22	)	,
(	10	,'Pat1',	'Drug3',	'2013-08-14',	'2014-01-23',	'162  ',	'120  ',	-42	)	,
(	11	,'Pat1',	'Drug3',	'2014-01-23',	'2014-04-02',	'69   ',	'120  ',	51	)	,
(	12	,'Pat1',	'Drug3',	'2014-04-02',	'2014-08-18',	'138  ',	'120  ',	-18	)	,
(	13	,'Pat1',	'Drug3',	'2014-08-18',	'2015-01-15',	'150  ',	'90   ',	-60	)	,
(	14	,'Pat1',	'Drug3',	'2015-01-15',	'2015-05-07',	'112  ',	'120  ',	8	)	,
(	15	,'Pat1',	'Drug3',	'2015-05-07',	NULL,			NULL,		'120  ',	120	)	



;WITH mytest as (
Select *, row_number() Over(partition by Patid, Drugid Order by ID)  rn from @t 

)
, mycte1 AS
(
  SELECT rn,  ID, Patid, Drugid, DateVal,NextDate,	Duration,qty, Remaining, AcumulativeTotal = Case When Remaining<0 then 0 else  Remaining end
    FROM mytest
    WHERE rn = 1
  UNION ALL
  SELECT t.rn, t.ID, t.Patid, t.Drugid,t.DateVal,t.NextDate,t.Duration,t.qty,  t.Remaining, Case when m.AcumulativeTotal + t.Remaining<= 0 Then 0 Else m.AcumulativeTotal + t.Remaining End 
   FROM mycte1 m 
   INNER JOIN mytest AS t
   ON t.rn = m.rn + 1 and t.Patid=m.Patid and t.Drugid=m.Drugid
)
SELECT ID, Patid, Drugid, DateVal,NextDate,	Duration,qty, Remaining, AcumulativeTotal
  FROM mycte1
  ORDER BY Patid, Drugid,ID
  OPTION (MAXRECURSION 200);
 

https://social.msdn.microsoft.com/Forums/en-US/6f14e822-eec3-43c8-9de3-753ba4366d0a/accumulative-surplus?forum=transactsql

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