;WITH temp_table AS(
SELECT 'A0001' Number,'AA1' PID,160 QTY,100 DeQty,21 Days
UNION ALL
SELECT 'A0001' Number,'AA1' PID,80 QTY,100 DeQty,21 Days
UNION ALL
SELECT 'A0001' Number,'AA1' PID,100 QTY,100 DeQty,19
UNION ALL
SELECT 'A0001' Number,'AA1' PID,10 QTY,100 DeQty,17 Days
UNION ALL
SELECT 'A0001' Number,'AA1' PID,180 QTY,100 DeQty,15 Days
),temp_table1 AS(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY temp_table.Days) Num,
SUM(temp_table.QTY) OVER() Sum_Qty
FROM temp_table
),temp_table2 AS(
SELECT
*
FROM(
SELECT
MAX(a.Number) Number,
MAX(a.QTY) QTY,
MAX(a.DeQty) DeQty,
MAX(a.Days) Days,
SUM(b.QTY) C_Qty,
MAX(a.Sum_Qty) Sum_Qty,
a.num
FROM temp_table1 a
JOIN temp_table1 b ON a.Num>=b.Num
GROUP BY a.Num
)t
OUTER APPLY(
SELECT SUM(c.QTY) O_Qty FROM temp_table1 c WHERE c.Num<t.Num
)g
)
SELECT
temp_table2.Number,
temp_table2.QTY,
temp_table2.DeQty,
temp_table2.Days,
CASE WHEN temp_table2.DeQty>=temp_table2.C_Qty
THEN temp_table2.Qty
WHEN temp_table2.DeQty<temp_table2.QTY AND temp_table2.O_Qty IS NULL
THEN temp_table2.DeQty
ELSE CASE WHEN temp_table2.C_Qty<=temp_table2.Sum_Qty
THEN CASE WHEN temp_table2.DeQty<temp_table2.O_Qty
THEN 0
ELSE temp_table2.DeQty-temp_table2.O_Qty
END
ELSE 0
END
END result
FROM temp_table2;