CREATE TABLE [dbo].[Stock](
[ID] [int] IDENTITY(1,1) NOT NULL, [Date] [nvarchar](50) NULL, [TransactionNo] [nvarchar](50) NULL, [ArticleID] [nvarchar](50) NULL, [Stock] [int] NULL
) ON [PRIMARY] GO
SET IDENTITY_INSERT [dbo].[Stock] ON
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (1, N'1-Jan-2013', N'GRN-1', N'A1', 100)
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (2, N'5-Jan-2013', N'GRN-2', N'A1', 200)
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (3, N'10-Jan-2013', N'GRN-1', N'A1', 60)
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (4, N'15-Jan-2013', N'GRN-1', N'A1', 10)
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (5, N'20-Jan-2013', N'GRN-3', N'A1', 300)
INSERT [dbo].[Stock] ([ID], [Date], [TransactionNo], [ArticleID], [Stock]) VALUES (6, N'25-Jan-2015', N'GRN-2', N'A1', 150)
SET IDENTITY_INSERT [dbo].[Stock] OFF
ID Date TransactionNo ArticleID Stock
1 1-Jan-2013 GRN-1 A1 100
2 5-Jan-2013 GRN-2 A1 200
3 10-Jan-2013 GRN-1 A1 60
4 15-Jan-2013 GRN-1 A1 10
5 20-Jan-2013 GRN-3 A1 300
6 25-Jan-2015 GRN-2 A1 150
There are 2 types of record in this table. The first type is whenever stock is received and the second type is for the remaining stock after dispatch. This is further explained:
Whenever any article is received in a warehouse, a record is inserted in stock table. Date is the Date on which article was received;Transaction No refers to the receiving transaction through which the article was received. Stock is the number of units of the article received. Records with ID 1,2,5 are such records.
whenever article is dispatched out of warehouse,a record is inserted in the stock table for the remaining stock. Records with ID 3,4,6 are such records.
For example in the data above, for record is the GRN-1, the receiving record with ID 1 and its dispatch records with ID 3&4. Originally through GRN1,100 units were received (ID1),then 40 were dispatched. So the remaining stock was 60(ID3). Then 5 were dispatched and the remaining stock was 10 (ID4).
How we create a stored procedure to get output like below:
Stored procedure output like below
input parameter
Date=2 Jan Article=A1 output=>[ 100]
Date=8 Jan Article=A1 output=>[300]
Date=11 Jan Article=A1 output=> [260]
Date=16 Jan Article=A1 output=>[210]
Date=21 Jan Article=A1 output=> [510]
Date=26 Jan Article=A1 output=> [460]