January 19, 2021 at 7:04 am
Hello All,
I have the following SQL Query, what I need is to have a column let's Say Shortages. since I arrange or Sort the Query by Item, so If Item No_ = Previous Item No i.e Item No_ of the previous record, the Shortages will Sill shortages - [remaining Qty_] else Shortages will be QOH- Remaining Qty
I attached an excel table also for this calculation, the formula used is
=IF(A2=A1,E1-D2,C2-D2)
Select
POC.[Prod_ Order No_] As [WO No_],
POL.[Item No_] as [Prod. Item No_],
POL.[Description] as [FG Decsription],
POL.[Unit of Measure Code] as [Prod. Order Item UOM],
POL.[Remaining Quantity] as [Prod. Order Quantity],
POL.[Due Date] as [Due Date(Prod. Order)],
POC.[Item No_] [Component No],
POC.[Description] as [Component Desc],
POC.[Manufacturer Code],
POC.[Mfg_ Part No_],
POC.[Due Date],
Item.[Buyer],
Item.[Customer No_],
case
When Item.[Replenishment System] = 0 then 'Purchase'
Else 'Prod. Order'
end as [Replenishment System],
(Select ISNULL(Sum(Quantity),0) from [dbo].[XXXXXXX$Item Ledger Entry] where [Item No_]= POC.[Item No_]) As QOH,
(Select ISNULL(Sum(Quantity),0) from [dbo].[XXXXXXX$Item Ledger Entry] where [Item No_]= POC.[Item No_] AND [Location Code] Like '%MRB%') As MRB_QTY,
POC.[Remaining Quantity],
(Select ISNULL(SUM([Outstanding Qty_ (Base)]),0) from [dbo].[XXXXXXX$Purchase Line] where [No_] = POC.[Item No_] AND [Document Type] = 1 AND [Type] = 2) as [Qty_on PO]
from dbo.[XXXXXXX$Prod_ Order Component] as POC
Inner Join dbo.[XXXXXXX$Prod_ Order Line] POL ON
(POC.[Status] = POL.[Status] ) AND
(POC.[Prod_ Order No_] = POL.[Prod_ Order No_]) AND
(POC.[Prod_ Order Line No_] = POL.[Line No_])
Inner Join dbo.[XXXXXXX$Item] as Item on
POC.[Item No_] = Item.[No_]
Where POC.[Status] in (1,2,3)
Order By POC.[Item No_],POC.[Variant Code],POC.[Location Code],POC.[Due Date],POC.[Status]
January 19, 2021 at 8:47 am
SQL is not Excel. By default, it cannot see the value in the previous or next row.
However, Window Functions are going to be your new best friend for this task, specifically the LAG function
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply