FIFO Inventory method in SQL Server

I'm looking for stock query based on GRN and GIN.

• GRN - Good Received Note
• GIN - Good Issued Note

The stock query column should be GRNItemCode, GRN_UnitPrice, GRN_Qty and that GRN_Qty should reduced once issue GIN against that GRN particular transaction or increase if GRN_Qty return for that transaction. below is the table structure.

Note that in stock query item code will/should repeat based on GRN transaction and after issued GIN against that transaction.

 

CREATE TABLE [dbo].[GRN](
    [GRN_No] [varchar](10) NULL,
    [GRN_Date] [datetime] NULL,
    [GRNItemCode] [varchar](10) NULL,
    [GRN_Qty] [int] NULL,
    [GRN_UnitPrice] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[GIN](
    [GIN_No] [nvarchar](50) NULL,
    [GIN_Date] [datetime] NULL,
    [GINItemCode] [varchar](10) NULL,
    [GIN_Qty] [int] NULL,
    [GIN_UnitPrice] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

 insert into GRN select 'SO-0001', '20120105 13:45', 'PROD-01', 50,25
insert into GRN select 'SO-0002', '20120108 12:00', 'PROD-02', 40,30
insert into GRN select 'SO-0003', '20120109 10:30', 'PROD-01', 20,26
insert into GRN select 'SO-0004', '20120110 17:10', 'PROD-03', 30,27

insert into GIN select 'PO-0001','20120115 15:00','PROD-01',30,25
insert into GIN select 'PO-0002','20120115 18:00','PROD-02',20,30
insert into GIN select 'PO-0003','20120116 18:00','PROD-01',30,26
 
 with s as (
 select
  *,
  GRNReceivedQty = (
   select sum(GRN_Qty) from GRN
   where GRNItemCode = s.GRNItemCode and GRN_Date <= s.GRN_Date
  )
 from GRN s
), p as (
 select GINItemCode, sum(GIN_Qty) as GINReceivedQty
 from GIN
 group by GINItemCode
)
select * from (
 select
  s.*,
  p.GINReceivedQty,
  case
   when s.GRNReceivedQty - isnull(p.GINReceivedQty,0) < 0 then 0
   when (s.GRNReceivedQty - isnull(p.GINReceivedQty,0) ) > s.GRN_Qty then s.GRN_Qty
   else s.GRNReceivedQty - isnull(p.GINReceivedQty,0)
  end as LeftQty
 from s
 left join p on s.GRNItemCode = p.GINItemCode
) fifo
where LeftQty > 0

 

Example GRNItemCode received 50 Qty and Unit Price 25 Once issued 30 qty and Unit Price 25 then in Query will show that ReceivedQty/Stock Qty is 20 and Unit Price 25 with GRN_No SO-001 Note that GRN S003 will show the same record becuas did not issue anything S003 that transaction. I want query to show all the GRN transaction with Qty and Unit Price, once qty issued against that transaction then reduce the qty

 

Regards,

Basit