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