Window Function

Question

Window Function

kiran 4243

Mr or Mrs. 500

Points: 553
More actions
February 20, 2019 at 4:44 am

#370720

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kiran 4243 Mr or Mrs. 500 Points: 553 More actions · Answer 2

Hello Experts,

Any feedback for my request. Appreciate your valuable feedback.

Best Regards,

Thom A SSC Guru Points: 99126 More actions · Answer 3

kiran 4243 - Thursday, February 21, 2019 1:18 AM
Hello 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