Reduce field value based on previous value on row

Question

I have a table like this:

Order Number  ItemCode   Quantity_Ordered   AvailableInShop
-----------------------------------------------------------
1             10         3                  3
2             10         2                  3

but I need a query to get this output:

Order Number  ItemCode   Quantity_Ordered   AvailableInShop
-----------------------------------------------------------
1             10         3                  3
2             10         2                  0

This is because the first sales order has taken all the quantity for subsequent ones.

Here is what i tried

select 
  [order number], 
  itemcode, 
  quantity_ordered, 
  availableInshop, 
  Row_Number() over (partition itemcode order by [order number] asc) Rownumber  
from Orders

Welcome to StackOverflow. This is not a code generator service. Please share us what did you try, whats wrong with it, and how can we help. Please show some effort to inspire the possible helpers (or pay for programming if you do not want to do it). Please read How do I ask a good question? article for more info. — Pred
– Pred, Commented Oct 2, 2014 at 16:39
My honest recommendation here is to not store inventory data and order data in the same table. Put item and inventory data in one table, and orders data in another. Then whenever a new order is placed, alter the inventory table to reflect the new inventory. This also lets you increase the quantity available without having to register a 'negative' order. — guildsbounty
– guildsbounty, Commented Oct 2, 2014 at 16:59

user3965303 · Accepted Answer · 2014-10-02 21:01:24Z

thank you for the motivation i finaly did as below;

DECLARE @Orders TABLE
            (
                DocEntry bigint,  DocDate datetime,  [Customer_Name] nvarchar(max),Itemcode nvarchar(50), [Order_Number] nvarchar(max)
                ,Quantity int, OnHand int ,RowNumber int
            );

            INSERT INTO @Orders
            select O.DocEntry,  o.DocDate,CardName,r.itemcode, o.NumAtCard [Order_Number], r.Quantity, 
            OnHand- (select sum(quantity) from rdr1 with (nolock)  where itemcode= r.itemcode and linestatus='o' and pickidNo is not null) OnHand
            , ROW_NUMBER() over (partition by r.itemcode 
                                                                                        order by o.docentry asc)
            from ORDR o join RDR1 r on o.DocEntry = r.DocEntry
            inner join OITW W with (nolock) on r.ItemCode=W.ItemCode and W.WhsCode=r.WhsCode  where 
          r.ItemCode ='1034356' and o.docstatus='o' and canceled='n' and r.pickidno is null
            order by 1          ;

             with mother as(  --cte produces unused_onhand
             select *, 

             case when (select top 1 OnHand -Quantity from @orders where RowNumber= r.RowNumber-1 and Itemcode=r.Itemcode )
             IS NUll 
             then r.OnHand --- for first row value append onHand
             else
             (select top 1 OnHand - ( select SUM(quantity ) from @Orders where Itemcode=r.Itemcode and RowNumber < r.RowNumber    ) from @orders where RowNumber= r.RowNumber-1 and Itemcode=r.Itemcode )
             end Unused_OnHand -- for subsequent onhand, append unused_onhand
             from @Orders r
             )  
             select *,           
             CASE when  (CAST( CAST( Unused_OnHand as  NUmeric(18,3)) / CAST( Quantity as  NUmeric(18,3) ) as NUmeric(18,3)) *100 ) > 100
                then CAST (100.00 AS numeric(18,3))

                when (CAST( CAST( Unused_OnHand as  NUmeric(18,3)) / CAST( Quantity as  NUmeric(18,3) ) as NUmeric(18,3)) *100 ) < 0
                then 0
                else
                (CAST( CAST( Unused_OnHand as  NUmeric(18,3)) / CAST( Quantity as  NUmeric(18,3) ) as NUmeric(18,3)) *100 ) 
                end[Fulfillment%] 
             from mother

Paul McLoughlin · Accepted Answer · 2014-10-02 23:06:53Z

Assuming that I've understood your question correctly, and that you've got a version of SQL Server that supports the LAG and FIRST_VALUE functions, then the T-SQL below is one possible way of doing what you want:

with myCte as 
(
    select o.oNum, o.itemCode, o.qOrdered, 
        sum(o.qOrdered) over (partition by o.itemCode order by o.oNum asc rows between unbounded preceding and current row) as totalOrdered,
        FIRST_VALUE(o.qAvailable) over (partition by o.itemCode order by o.oNum asc) as initialAvailability
    from dbo.Orders as o
),

myCte2 as
(
    select *, case when m.initialAvailability-m.totalOrdered < 0 then 0 else m.initialAvailability-m.totalOrdered end as nextAvailability
    from myCte as m
),

myCte3 as
(
    select *,
        lag(m.nextAvailability) over (partition by m.itemCode order by m.oNum asc) as prevAvailability
    from myCte2 as m
)

select m.oNum, m.itemCode, m.qOrdered, case when m.prevAvailability is null then m.initialAvailability else m.prevAvailability end as qAvailable
from myCte3 as m

Of course I agree with others that not storing the availability in the same record as the order quantity would be a more sensible long-term approach.

Collectives™ on Stack Overflow

Reduce field value based on previous value on row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related