It is unclear what is the logic behind the scene to calculate AumulativeTotal column? Qty-Duration?

Always state what version you are using..

Hi Aku,

Have you tried using a subquery? The only tricky part is to show a 0 when the total is negative and then carrying on from there. You may have to create a custom procedure for that. Just a thought...

• Edited by the DB guyMVP 16 hours 26 minutes ago

Hi Uri,

Sorry if I've confused thing in my attempt not to complicate it.  Duration is the number of days between prescriptions. Row 1, on the 25/10/2012 patient was prescribed 120 days of a particular medicine. Patient next prescription on 28/02/2013 is 126 days since the last prescription.  120 126 = -6 remaining.  The logic for the accumulative column is not to account for when the patient has no medication as minus but the patient surplus medication like 13/01/2014 when patient didnt receive supply because there was enough until the next attendance

I hope this makes it a bit clearer

Hi Aku,

Have you tried using a subquery? The only tricky part is to show a 0 when the total is negative and then carrying on from there. You may have to create a custom procedure for that. Just a thought...

• Edited by the DB guyMVP Monday, July 06, 2015 2:40 PM

Why the surplus of 25/10/2012 is 0 rather than -6?

Hi Aku,

Can you post your desired output that you are expecting from the above input table ?

Regards,

You can use quirky update method for this

see illustration for your sample data below

set dateformat dmy
go

declare @t table
(
ID	int,
DateVal datetime,
NextDate datetime,
Duration int,
qty	int,
FinishDate datetime,
Remaining int,
Surplus int,
AcumulativeTotal int
)

insert @t
values
(1,'25/10/2012','28/02/2013',126,120,'22/02/2013',-6,	0,	0),
(2,'28/02/2013','20/06/2013',	112,	112,	'20/06/2013',	0,	0,	0),
(3,'20/06/2013','08/10/2013',	110	,120,	'18/10/2013',	10,	10	,0),
(4,'08/10/2013','13/01/2014',	97,	120	,'05/02/2014',	23,	33,	0),
(5,'13/01/2014','28/01/2014',	15	 ,null,	null, 	-15	,18,	0),
(6,'28/01/2014','03/02/2014',	6,	120	,'28/05/2014',	114	,132,	0),
(7,'03/02/2014','07/02/2014',	4,	14	,'17/02/2014',	10	,142	,0),
(8,'07/02/2014','12/08/2014',	186	,null,null, 	 	-186,	-44,	0),
(9,'12/08/2014','02/12/2014',	112,	120	,'10/12/2014',	8,	-36,	0),
(10,'02/12/2014','22/03/2015',	110,	120,	'01/04/2015',	10	,-26	,0),
(11,'22/03/2015','26/03/2015',	4,	14,	'05/04/2015',	10	,-16	,0),
(12,'26/03/2015','17/04/2015',	22,	14,	'09/04/2015',	-8	,-16	,0),
(13,'17/04/2015',null,	 	120,null,	'15/08/2015',	120,	104,	0)

--Create workng table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
SELECT *,CAST(0 AS int) AS FinalSurplus INTO #temp
FROM @t

CREATE CLUSTERED INDEX IDX_Clust ON #temp (ID,DateVal)

--initialize variables
declare @ID int,@Finalsurplus int,@DateVal datetime

SELECT TOP 1 @ID = ID,@DateVal = DateVal,@Finalsurplus = Surplus
FROM #temp 
ORDER BY ID,DateVal


--Do quirky update
UPDATE t
SET @Finalsurplus = FinalSurplus = CASE WHEN ID = @ID AND DateVal = @dateVal THEN @Finalsurplus
                                  WHEN @Finalsurplus + Remaining < 0 THEN 0
                        ELSE @Finalsurplus + Remaining
                    END,
@ID = ID,
@DateVal = DateVal
FROM #temp t WITH (TABLOCKX)

OPTION (MAXDOP 1)

--update final table
UPDATE t
SET AcumulativeTotal = FinalSurplus
FROM @t t
INNER JOIN #temp tmp
On tmp.ID = t.ID
AND tmp.DateVal = t.DateVal

SELECT * FROM @t

output is below

ID	DateVal	NextDate	Duration	qty	FinishDate	Remaining	Surplus	AcumulativeTotal
1	2012-10-25 00:00:00.000	2013-02-28 00:00:00.000	126	120	2013-02-22 00:00:00.000	-6	0	0
2	2013-02-28 00:00:00.000	2013-06-20 00:00:00.000	112	112	2013-06-20 00:00:00.000	0	0	0
3	2013-06-20 00:00:00.000	2013-10-08 00:00:00.000	110	120	2013-10-18 00:00:00.000	10	10	10
4	2013-10-08 00:00:00.000	2014-01-13 00:00:00.000	97	120	2014-02-05 00:00:00.000	23	33	33
5	2014-01-13 00:00:00.000	2014-01-28 00:00:00.000	15	NULL	NULL	-15	18	18
6	2014-01-28 00:00:00.000	2014-02-03 00:00:00.000	6	120	2014-05-28 00:00:00.000	114	132	132
7	2014-02-03 00:00:00.000	2014-02-07 00:00:00.000	4	14	2014-02-17 00:00:00.000	10	142	142
8	2014-02-07 00:00:00.000	2014-08-12 00:00:00.000	186	NULL	NULL	-186	-44	0
9	2014-08-12 00:00:00.000	2014-12-02 00:00:00.000	112	120	2014-12-10 00:00:00.000	8	-36	8
10	2014-12-02 00:00:00.000	2015-03-22 00:00:00.000	110	120	2015-04-01 00:00:00.000	10	-26	18
11	2015-03-22 00:00:00.000	2015-03-26 00:00:00.000	4	14	2015-04-05 00:00:00.000	10	-16	28
12	2015-03-26 00:00:00.000	2015-04-17 00:00:00.000	22	14	2015-04-09 00:00:00.000	-8	-16	20
13	2015-04-17 00:00:00.000	NULL	120	NULL	2015-08-15 00:00:00.000	120	104	140

Refer below for more details

http://visakhm.blogspot.ae/2010/03/using-quirky-updates-to-develop-well.html

Hi HoroChan,

Because I am only interested in how much drug the patients have, not what the patient have missed.  Medication cannot be given for past dates.  So as of 25/10/2012 patient didn't take medication for the last 6 days.  Zero, because patient didn't have any, patient run out of the medication.

I hope this is clearer.

thank you

Aku

Hi Milan Das,

The desired output is the 'AcumulativeTotal' the last column of the table i posted.

thank you

Aku

Hi Visakh16, 

Thank you very much for your effort with this.  I ws hoping not have to perform any action query. 

I've being trying the running total methods like this:

Select 
	a.id,b.ID,a.DateVal, a.Remaining, b.Remaining,
	Sum(a.Remaining)Over (Order by a.id) as R1, 
	CASE
		When Sum(a.Remaining)Over (Order by a.id) <0 then 0
		Else Sum(a.Remaining)Over (Order by a.id)
	End as R2,
	 Sum(CASE
			When a.Remaining <0 Then 0
			Else b.Remaining
		 ENd 	)Over (Order by a.id) as R3
	
from #temp a Left JOIN #temp b
on a.id = b.id +1

From your above temp table.  I think the solution is here but just out of my reach, please take a look.

and thank you again.

Aku

Thanks for Visakh16's sample data table.

set dateformat dmy
go

declare @t table
(
ID	int,
DateVal datetime,
NextDate datetime,
Duration int,
qty	int,
FinishDate datetime,
Remaining int,
Surplus int,
AcumulativeTotal int
)

insert @t
values
(1,'25/10/2012','28/02/2013',126,120,'22/02/2013',-6,	0,	0),
(2,'28/02/2013','20/06/2013',	112,	112,	'20/06/2013',	0,	0,	0),
(3,'20/06/2013','08/10/2013',	110	,120,	'18/10/2013',	10,	10	,0),
(4,'08/10/2013','13/01/2014',	97,	120	,'05/02/2014',	23,	33,	0),
(5,'13/01/2014','28/01/2014',	15	 ,null,	null, 	-15	,18,	0),
(6,'28/01/2014','03/02/2014',	6,	120	,'28/05/2014',	114	,132,	0),
(7,'03/02/2014','07/02/2014',	4,	14	,'17/02/2014',	10	,142	,0),
(8,'07/02/2014','12/08/2014',	186	,null,null, 	 	-186,	-44,	0),
(9,'12/08/2014','02/12/2014',	112,	120	,'10/12/2014',	8,	-36,	0),
(10,'02/12/2014','22/03/2015',	110,	120,	'01/04/2015',	10	,-26	,0),
(11,'22/03/2015','26/03/2015',	4,	14,	'05/04/2015',	10	,-16	,0),
(12,'26/03/2015','17/04/2015',	22,	14,	'09/04/2015',	-8	,-16	,0),
(13,'17/04/2015',null,	 	120,null,	'15/08/2015',	120,	104,	0)
;with mycte as (
Select ID,DateVal,NextDate,	Duration,	qty, FinishDate, Remaining,	Surplus,
Sum(Remaining)Over (Order by id) as R1, 
 CASE When Sum(Remaining)Over (Order by id) <0 then 0
		Else Remaining
	End as R2 	
from @t   )

,mycte2 as ( 
Select *,   Sum( Case when Remaining>= R2 or Remaining>= R1 Then  0 Else 1 End) Over(Order by id) grp,
   ISNULL(Nullif(R2,0),Case when Remaining<= R1 Then  0 Else Remaining End )    R3 
from mycte )

Select ID,DateVal,NextDate,	Duration,	qty, FinishDate, Remaining,	Surplus
, sum(R3) Over(Partition by grp Order by id) AcumulativeTotal   
from mycte2

/*
ID	DateVal	NextDate	Duration	qty	FinishDate	Remaining	Surplus	AcumulativeTotal
1	2012-10-25 00:00:00.000	2013-02-28 00:00:00.000	126	120	2013-02-22 00:00:00.000	-6	0	0
2	2013-02-28 00:00:00.000	2013-06-20 00:00:00.000	112	112	2013-06-20 00:00:00.000	0	0	0
3	2013-06-20 00:00:00.000	2013-10-08 00:00:00.000	110	120	2013-10-18 00:00:00.000	10	10	10
4	2013-10-08 00:00:00.000	2014-01-13 00:00:00.000	97	120	2014-02-05 00:00:00.000	23	33	33
5	2014-01-13 00:00:00.000	2014-01-28 00:00:00.000	15	NULL	NULL	-15	18	18
6	2014-01-28 00:00:00.000	2014-02-03 00:00:00.000	6	120	2014-05-28 00:00:00.000	114	132	132
7	2014-02-03 00:00:00.000	2014-02-07 00:00:00.000	4	14	2014-02-17 00:00:00.000	10	142	142
8	2014-02-07 00:00:00.000	2014-08-12 00:00:00.000	186	NULL	NULL	-186	-44	0
9	2014-08-12 00:00:00.000	2014-12-02 00:00:00.000	112	120	2014-12-10 00:00:00.000	8	-36	8
10	2014-12-02 00:00:00.000	2015-03-22 00:00:00.000	110	120	2015-04-01 00:00:00.000	10	-26	18
11	2015-03-22 00:00:00.000	2015-03-26 00:00:00.000	4	14	2015-04-05 00:00:00.000	10	-16	28
12	2015-03-26 00:00:00.000	2015-04-17 00:00:00.000	22	14	2015-04-09 00:00:00.000	-8	-16	20
13	2015-04-17 00:00:00.000	NULL	120	NULL	2015-08-15 00:00:00.000	120	104	140

*/

 

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator Wednesday, July 08, 2015 5:46 AM
• Marked as answer by AkuYali Wednesday, July 08, 2015 10:47 AM
• Unmarked as answer by AkuYali 21 hours 15 minutes ago

Hi Jingyang,

As much as the above solution is correct for the sample data I posted, the entire dataset is much larger.  When I applied you solution the rest of the dataset, it didnt work as well.

Here is a slightly larger sample data

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	)	
--Select * from @t
;with mycte as (
	Select 
		ID, Patid, Drugid, DateVal,NextDate,	Duration,qty, Remaining,	--Surplus,FinishDate,
		Sum(Remaining)Over (Partition by Patid, Drugid Order by id) as R1, 
	CASE 
		When Sum(Remaining)Over (Partition by Patid, Drugid Order by id) <0 then 0
		Else Remaining
	End as R2 	
from @t   )
,mycte2 as ( 
Select *,   Sum( Case when Remaining>= R2 or Remaining>= R1 Then  0 Else 1 End) Over(Partition by Patid, Drugid Order by id) grp,
   ISNULL(Nullif(R2,0),Case when Remaining<= R1 Then  0 Else Remaining End )    R3 
from mycte )
Select ID, Patid, Drugid, DateVal, NextDate,Duration,qty, Remaining,	--Surplus FinishDate,
	sum(R3) Over(Partition by Patid, Drugid, grp Order by id) AcumulativeTotal   
from mycte2
Order by 2,3,1

Here is the result with the desired effect:

ID	Patid	Drugid	DateVal	NextDate	Duration	qty	Remaining	AcumulativeTotal	DisiredEffect
1	Pat1      	Drug3     	27/06/2012	08/08/2012	42	60	18	18	18
2	Pat1      	Drug3     	08/08/2012	29/08/2012	21	NULL	-21	0	0
3	Pat1      	Drug3     	29/08/2012	24/09/2012	26	30	4	4	4
4	Pat1      	Drug3     	24/09/2012	02/12/2012	69	60	-9	0	0
5	Pat1      	Drug3     	02/12/2012	19/12/2012	17	30	13	13	13
6	Pat1      	Drug3     	19/12/2012	30/01/2013	42	120	78	91	104
7	Pat1      	Drug3     	30/01/2013	27/02/2013	28	NULL	-28	63	167
8	Pat1      	Drug3     	27/02/2013	24/04/2013	56	30	-26	37	204
9	Pat1      	Drug3     	24/04/2013	14/08/2013	112	90	-22	15	219
10	Pat1      	Drug3     	14/08/2013	23/01/2014	162	120	-42	0	219
11	Pat1      	Drug3     	23/01/2014	02/04/2014	69	120	51	51	270
12	Pat1      	Drug3     	02/04/2014	18/08/2014	138	120	-18	0	270
13	Pat1      	Drug3     	18/08/2014	15/01/2015	150	90	-60	-60	210
14	Pat1      	Drug3     	15/01/2015	07/05/2015	112	120	8	-52	158
15	Pat1      	Drug3     	07/05/2015	NULL	NULL	120	120	68	226
1	Pat2      	Drug1     	25/10/2012	28/02/2013	126	120	-6	0	0
2	Pat2      	Drug1     	28/02/2013	20/06/2013	112	112	0	0	0
3	Pat2      	Drug1     	20/06/2013	08/10/2013	110	120	10	10	10
4	Pat2      	Drug1     	08/10/2013	13/01/2014	97	120	23	33	33
5	Pat2      	Drug1     	13/01/2014	28/01/2014	15	NULL	-15	18	18
6	Pat2      	Drug1     	28/01/2014	03/02/2014	6	120	114	132	132
7	Pat2      	Drug1     	03/02/2014	07/02/2014	4	14	10	142	142
8	Pat2      	Drug1     	07/02/2014	12/08/2014	186	NULL	-186	0	0
9	Pat2      	Drug1     	12/08/2014	02/12/2014	112	120	8	8	8
10	Pat2      	Drug1     	02/12/2014	22/03/2015	110	120	10	18	18
11	Pat2      	Drug1     	22/03/2015	26/03/2015	4	14	10	28	28
12	Pat2      	Drug1     	26/03/2015	17/04/2015	22	14	-8	20	20
13	Pat2      	Drug1     	17/04/2015	NULL	NULL	120	120	140	140
1	Pat2      	Drug2     	02/02/2011	04/02/2011	2	1	-1	0	0
2	Pat2      	Drug2     	04/02/2011	20/06/2011	136	NULL	-136	-136	0
3	Pat2      	Drug2     	20/06/2011	19/11/2012	518	NULL	-518	-654	0
4	Pat2      	Drug2     	19/11/2012	27/11/2012	8	7	-1	-655	0
5	Pat2      	Drug2     	27/11/2012	10/01/2014	409	90	-319	-974	0
6	Pat2      	Drug2     	10/01/2014	11/01/2014	1	14	13	-961	13
7	Pat2      	Drug2     	11/01/2014	03/02/2014	23	14	-9	-970	4
8	Pat2      	Drug2     	03/02/2014	07/02/2014	4	14	10	-960	14
9	Pat2      	Drug2     	07/02/2014	21/03/2015	407	NULL	-407	-1367	0
10	Pat2      	Drug2     	21/03/2015	22/03/2015	1	14	13	-1354	13
11	Pat2      	Drug2     	22/03/2015	26/03/2015	4	14	10	-1344	23
12	Pat2      	Drug2     	26/03/2015	NULL	NULL	14	14	-1330	37

All are welcome to take another look, please.

thank you

Aku

Akuyali

• Edited by AkuYali Wednesday, July 08, 2015 4:43 PM

Please take another look...

thank you

Aku

Just look at the query with where Patid='Pat1', you will find your current expected result does not meet your initial requirement

This was the initial example:

Select * from @t 
where patid = 'pat2' and DrugID = 'Drug1'

Jingyang,

Please explain what you think is wrong with it, i am unsure.

thank you

Aku

I checked where patid = 'pat2' and DrugID = 'Drug1'

and it should work with your requirement.

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	)	
--Select * from @t
;with mycte as (
	Select 
		ID, Patid, Drugid, DateVal,NextDate,	Duration,qty, Remaining,	--Surplus,FinishDate,
		Sum(Remaining)Over (Partition by Patid, Drugid Order by id) as R1, 
	CASE 
		When Sum(Remaining)Over (Partition by Patid, Drugid Order by id) <0 then 0
		Else Remaining
	End as R2 	
from @t  
where patid = 'pat2' and DrugID = 'Drug1'
 )
,mycte2 as ( 
Select *,   Sum( Case when Remaining>= R2 or Remaining>= R1 Then  0 Else 1 End) Over(Partition by Patid, Drugid Order by id) grp,
   ISNULL(Nullif(R2,0),Case when Remaining<= R1 Then  0 Else Remaining End )    R3 
from mycte )
Select ID, Patid, Drugid, DateVal, NextDate,Duration,qty, Remaining,	--Surplus FinishDate,
	sum(R3) Over(Partition by Patid, Drugid, grp Order by id) AcumulativeTotal   
from mycte2
Order by 2,3,1

Select * from @t 
where patid = 'pat2' and DrugID = 'Drug1'

Yes, but it doen't work for:

there shouldn't be any negative numbers in the Accumulatice total, only zeros and positive numbers.

Thank you

Aku

Select * from @t 
where patid = 'pat2' and DrugID = 'Drug2'	

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 mycte as (
	Select 
		ID, Patid, Drugid, DateVal,NextDate,	Duration,qty, Remaining,	--Surplus,FinishDate,
		
		Sum(Remaining)Over (Partition by Patid, Drugid Order by id) as R1, 
	CASE 
		When Sum(Remaining)Over (Partition by Patid, Drugid, Sign(Remaining)  Order by id) < 0 then 0
		Else Remaining
	End as R2,	

	Lag(Remaining) Over(Partition by Patid, Drugid Order by id)  L1

from @t  
 
)

 

,mycte2 as ( 
Select *, Case when  Remaining + L1>0  or R1 >0 Then Remaining Else R2 End  newR2, 

Sum( Case when (
Case when (R2=0 And Remaining+L1 > 0 ) or R1 >0 Then Remaining Else R2 End)  = 0 
then 1 else 0 End
) 
Over(Partition by  Patid, Drugid Order By  id ) grp
 
from mycte )


Select ID, Patid, Drugid, DateVal, NextDate,Duration,qty, Remaining,
	sum(NewR2) Over(Partition by Patid, Drugid, grp Order by id) AcumulativeTotal  
 
from mycte2
Order by Patid, Drugid,ID
/*
ID	Patid	Drugid	DateVal	NextDate	Duration	qty	Remaining	AcumulativeTotal
1	Pat1      	Drug3     	2012-06-27	2012-08-08	42        	60        	18	18
2	Pat1      	Drug3     	2012-08-08	2012-08-29	21        	NULL	-21	0
3	Pat1      	Drug3     	2012-08-29	2012-09-24	26        	30        	4	4
4	Pat1      	Drug3     	2012-09-24	2012-12-02	69        	60        	-9	0
5	Pat1      	Drug3     	2012-12-02	2012-12-19	17        	30        	13	13
6	Pat1      	Drug3     	2012-12-19	2013-01-30	42        	120       	78	91
7	Pat1      	Drug3     	2013-01-30	2013-02-27	28        	NULL	-28	63
8	Pat1      	Drug3     	2013-02-27	2013-04-24	56        	30        	-26	37
9	Pat1      	Drug3     	2013-04-24	2013-08-14	112       	90        	-22	15
10	Pat1      	Drug3     	2013-08-14	2014-01-23	162       	120       	-42	0
11	Pat1      	Drug3     	2014-01-23	2014-04-02	69        	120       	51	51
12	Pat1      	Drug3     	2014-04-02	2014-08-18	138       	120       	-18	33
13	Pat1      	Drug3     	2014-08-18	2015-01-15	150       	90        	-60	0
14	Pat1      	Drug3     	2015-01-15	2015-05-07	112       	120       	8	8
15	Pat1      	Drug3     	2015-05-07	NULL	NULL	120       	120	128
1	Pat2      	Drug1     	2012-10-25	2013-02-28	126       	120       	-6	0
2	Pat2      	Drug1     	2013-02-28	2013-06-20	112       	112       	0	0
3	Pat2      	Drug1     	2013-06-20	2013-10-08	110       	120       	10	10
4	Pat2      	Drug1     	2013-10-08	2014-01-13	97        	120       	23	33
5	Pat2      	Drug1     	2014-01-13	2014-01-28	15        	NULL	-15	18
6	Pat2      	Drug1     	2014-01-28	2014-02-03	6         	120       	114	132
7	Pat2      	Drug1     	2014-02-03	2014-02-07	4         	14        	10	142
8	Pat2      	Drug1     	2014-02-07	2014-08-12	186       	NULL	-186	0
9	Pat2      	Drug1     	2014-08-12	2014-12-02	112       	120       	8	8
10	Pat2      	Drug1     	2014-12-02	2015-03-22	110       	120       	10	18
11	Pat2      	Drug1     	2015-03-22	2015-03-26	4         	14        	10	28
12	Pat2      	Drug1     	2015-03-26	2015-04-17	22        	14        	-8	20
13	Pat2      	Drug1     	2015-04-17	NULL	NULL	120       	120	140
1	Pat2      	Drug2     	2011-02-02	2011-02-04	2         	1         	-1	0
2	Pat2      	Drug2     	2011-02-04	2011-06-20	136       	NULL	-136	0
3	Pat2      	Drug2     	2011-06-20	2012-11-19	518       	NULL	-518	0
4	Pat2      	Drug2     	2012-11-19	2012-11-27	8         	7         	-1	0
5	Pat2      	Drug2     	2012-11-27	2014-01-10	409       	90        	-319	0
6	Pat2      	Drug2     	2014-01-10	2014-01-11	1         	14        	13	13
7	Pat2      	Drug2     	2014-01-11	2014-02-03	23        	14        	-9	4
8	Pat2      	Drug2     	2014-02-03	2014-02-07	4         	14        	10	14
9	Pat2      	Drug2     	2014-02-07	2015-03-21	407       	NULL	-407	0
10	Pat2      	Drug2     	2015-03-21	2015-03-22	1         	14        	13	13
11	Pat2      	Drug2     	2015-03-22	2015-03-26	4         	14        	10	23
12	Pat2      	Drug2     	2015-03-26	NULL	NULL	14        	14	37
*/