Hi,

Try this

declare @priceTable table (unitPrice money, finalQty int);

with cte as (select *, row_number() over (order by date_rec) as Rn from myTable)

update cte set qty_left = qty_left - @QtyUse 
output inserted.unitPrice, inserted qty_left into @PriceTable (unitPrice, FinalQty)
where Rn = 1;

select * from @PriceTable; -- final qty and unit price 

However, what do you want when @QtyUse is greater than qty_left? I assume you want to keep subtracting it from other rows - this may be slightly more tricky problem.


>Hi,
>
>This is a simplified case.
>
>The table (structure and sample data is as follows)
>

>PK      PART_PK   DATE_REC    QTY_LEFT    REC_ID      UNIT_PRICE
>1       1         01/01/2019     10         '1'         4.00
>2       1         02/01/2019     4          '1'         5.00
>3       1         12/14/2018     7          '1'         5.00
>4       1         04/05/2017     0          '2'         3.50
>
>

>
>I am trying to create a SQL that would do the following:
>1. A parameter is passed as follows:
>@QtyUse as Int
>The value of @QtyUse could be from 0 to any number. For example, 1
>2. Find the oldest record in the above table (date 12/14/2018)
>Reduce the value in QTY_LEFT by the @QtyUse and update the @QtyUse
>3. Set the value of a variable @UnitPrice to the value of the column UNIT_PRICE (of the effected record)
>
>Here is my attempt (not successful)
>

>declare @QtyUse as Int
>set @QtyUse = 1
>update t1 set t1.qty_left = t1.qty_left -  @QtyUse (select top 1 t2.pk from MyTable t2 where t2.rec_id = '1' 
>   order by t2.date_rec) , from MyTable t1 where t1.pk = t2.pk
>

>
>Please let me know how to change the above SQL?
>
>TIA