how to write a stored procedure to achieve below stock detai

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]

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra11y ago
Try below query:

SELECT SUM(Stock)
FROM(
   SELECT *, ROW_NUMBER() OVER(Partition by TransactionNo ORDER BY ID DESC) AS TranNo
   from [Stock]
   WHERE ArticleID = 'A1'
   AND ((CAST(SUBSTRING([date], 1, CHARINDEX('-', [date])-1) AS Integer) <= 26) AND    ((CHARINDEX('Jan', [date])-1) > 0))
) AS Temp
WHERE Temp.TranNo = 1;

You need to change month and date as per your requirement in query which is marked in yellow.

Note: Due to date datatype is nvarchar so query is used some string operation functions.
0