I have two tables named 'Purchase' and 'ClosingStock'

Sample tables in dbfiddle is attached for quick actions.

Table: Purchase Table

 ---------- -------------- ----- ----------- 
| ItemName | PurchaseDate | QTY | CostPrice |
 ---------- -------------- ----- ----------- 
| ItemA    | 2022-03-14   |  20 | 32.00     |
| ItemA    | 2022-04-28   |   7 | 30.00     |
| ItemA    | 2022-06-17   |  33 | 25.00     |
|          |              |     |           |
| ItemB    | 2022-05-16   |  65 | 50.00     |
 ---------- -------------- ----- ----------- 

Table: ClosingStock

 ---------- -------------- 
| ItemName | ClosingStock |
 ---------- -------------- 
| ItemA    |           35 |
| ItemB    |           60 |
 ---------- -------------- 

Required Result

 ---------- -------------- ------------ 
| ItemName | ClosingStock | TotalValue |
 ---------- -------------- ------------ 
| ItemA    |           35 |        885 |
| ItemB    |           60 |       3000 |
 ---------- -------------- ------------ 

The required query should return the 'TotalValue' based on bottom-up method

for example:

In my sample dataset, ItemA has ClosingQTY = 35

To calculate TotalValue for ItemA, it should check the last record in Purchase table (QTY column). Here it is 33.

So out of 35 ClosingQTY, 33 pieces would have value (33 * 25). For the remaining 2 qty of ItemA, it should look for 2nd last record in Purchase. So it would be (2 * 30).

Math would be (33 * 25) (2 * 30) = 885

So for total 35 qty of ItemA, TotalValue would be 885. similar calculation for ItemB and so on.

https://dbfiddle.uk/plwwAGr6

CodePudding user response：

You may use join, calculate running sum of QTY, compare it with ClosingStock and take only a part from QTY when running sum becomes greater than ClosingStock. Then aggregate calculated quantity multiplied by a price.

with purchase(ItemName, PurchaseDate, QTY, CostPrice) as (
  select 'ItemA', convert(date, '2022-03-14'), 20, 32.00 union all
  select 'ItemA', convert(date, '2022-04-28'),  7, 30.00 union all
  select 'ItemA', convert(date, '2022-06-17'), 33, 25.00 union all
  select 'ItemB', convert(date, '2022-05-16'), 65, 50.00
)
, stock(ItemName, ClosingStock) as (
  select 'ItemA', 35 union all
  select 'ItemB', 60
)
select
  itemname
  , sum(TotalValue ) as TotalValue
from (
  select
    p.itemname
    , case
        /*Running total is less than stock - take purchase in full*/
        when s.ClosingStock
          >= sum(p.qty) over(
               partition by p.itemname order by PurchaseDate desc
             )
        then p.qty
        /*Otherwise if previous running total is less then stock - take only part*/
        when s.ClosingStock - (
          sum(p.qty) over(
            partition by p.itemname order by PurchaseDate desc
          ) - p.qty
        ) > 0
        then s.ClosingStock - (sum(p.qty) over(partition by p.itemname order by PurchaseDate desc) - p.qty)
        /*When previous running total is greater than stock, then do not account this purchase*/
    end * p.costprice as TotalValue 
    , p.purchasedate
    , s.closingstock
  from purchase as p
    join stock as s
    on p.ItemName = s.ItemName
) as t
group by itemname

fiddle

CodePudding user response：

Both solutions work (by IPTR and astentx)

It seems method by IPTS more efficient as it uses "unbounded preceding"

select ItemName, ClosingQTY
  ,sum(case when rt <= ClosingQTY then QTY else ClosingQTY - (rt - QTY) end * CostPrice) 
from 
  (
    select 
      c.ClosingQTY, 
      p.*, 
      sum(p.QTY) over(partition by p.ItemName order by p.PurchaseDate desc rows unbounded preceding) as rt 
    from 
      ClosingStock as c 
      join Purchase as p on c.ItemName = p.ItemName
  ) as r 
where rt - QTY < ClosingQTY 
group by ItemName, ClosingQTY