February 20, 2019 at 4:44 am
Hello,
Good evening.
I am trying to find the latest checking lot no based on each receiving which matches with available with inventory level. Please refer to the below picture for your kind perusal.
For example as per the image:
GJ-11001: current inventory qty is 698.59 and recent checking lot no qty is 3600 which means inventory qty is available in latest lot no so need to show this row and
GJ-11029: current inventory qty is 4950.38 as per highlighted need those 3 rows to show inventory qty is available in those highlighted rows.
I tried using my query. Please help to get desired result or suggest a better approach to accomplish this task.;WITH cteLot as (
select p.StockCode
,p.Description
,d.CheckingLotNo
,r.ExpiryDate
,(r.InspectedQty-r.RejectQty) AS [ReleasedQty]
,dbo.fn_StockInFg(p.Uniid) AS [StockOnhand]
,dbo.fn_StockInFg(p.Uniid) - sum(r.InspectedQty) OVER (PARTITION BY P.UNIID order by r.ExpiryDate desc ROWS UNBOUNDED PRECEDING) AS RunningOnhandQty
,ROW_NUMBER() OVER (partition by p.stockcode order by r.expirydate desc) as RowNumber
from rawmaterialinspection as r
inner join PurchaseReceivingDet as d
on d.Uniid = r.CheckingLotNo
inner join stockitem as p
on d.StockCode = p.Uniid
where (p.TypeID = 10)
and (dbo.fn_StockInFg(p.Uniid) > 0)
and (ExpiryDate IS NOT NULL)
)
select CONCAT(StockCode,' - ',Description) AS Product
,CheckingLotNo
,CONVERT(varchar(10),ExpiryDate,103) AS ExpiryDate
,ReleasedQty
,StockOnhand
,RunningOnhandQty
,RowNumber
from cteLot
.
Thank you.
February 20, 2019 at 12:32 pm
If that query returns the correct result, I would just suggest to change the scalar UDF into an Inline Table-Valued Function. There's an explanation on how to do it in here: http://www.sqlservercentral.com/articles/T-SQL/91724/
There are more efficient ways to get the running total, but I would suggest to stay with the current version unless a major problem exists.
February 21, 2019 at 1:18 am
Hello Experts,
Any feedback for my request. Appreciate your valuable feedback.
Best Regards,
February 21, 2019 at 3:25 am
kiran 4243 - Thursday, February 21, 2019 1:18 AMHello Experts,Any feedback for my request. Appreciate your valuable feedback.
Best Regards,
What was wrong with Luis' feedback? I agree that, if you can, changing fn_StockInFg from a Scalar Function to an Inline Table Table Function will probably give you a performance benefit, but other than that, I don't specifically see anything "wrong". Unless you know there's something wrong and having told us what?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply