Hi,
I'm looking for stock query based on GRN and GIN, the query column should be ItemCode, GRNUnit Price, GRNQty and that GRNQty should reduced once issue GIN against that GRN particular transaction or increase if GRNQty 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.
GRN-Good Received Note
GIN - Good Issued Note
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
below query is showing the qty wise and working once qty issued and return.
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
Thanks
Basit.