Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.
--drop table #TempStk
CREATE TABLE #TempStk
( [SEQ] INT ,
[ID] INT NOT NULL
,[Pricelist] nvarchar(300)
,[Status] NVARCHAR(50) NULL
,[Date] DATETIME
,[Quantity] INT
,[Balance] INT NULL
)
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(1,8100,'ACTIFED','Receipt','01/01/2004',100);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(2,8100,'ACTIFED','Issued','03/28/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(3,8100,'ACTIFED','Issued','04/01/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(4,8100,'ACTIFED','Issued','05/05/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(5,8100,'ACTIFED','Issued','05/10/2004',5);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(6,8110,'AGIOLAX','Receipt','01/01/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(7,8110,'AGIOLAX','Issued','02/12/2004',10);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(8,8110,'AGIOLAX','Receipt','03/28/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(9,8110,'AGIOLAX','Issued','05/05/2004',25);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(10,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','01/01/2004',200);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(11,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','03/28/2004',50);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(12,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','04/01/2004',15);
GO
INSERT INTO #TempStk
( [SEQ] ,
[ID]
,[Pricelist]
,[Status]
,[Date]
,[Quantity]
)
values
(13,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','05/05/2004',10);
GO
select * from #TempStk
create clustered index IX_RunningBal on #TempStk (
ID,
Date);
declare @Balance int,
@ID int;
set @ID = 0;
update #TempStk set
@Balance = [Balance] = case when @ID <> ts.ID
then 0
else @Balance
end + (cast(case when [Status] = 'Receipt'
then 1
when [Status] = 'Issued'
then -1
end as int) * ts.[Quantity]),
@ID = ts.[ID]
from
#TempStk ts with (index = 1)
select * from #TempStk;