I'm looking for stock query based on GRN and GIN.
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]
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