Without DDL, consumable test data and it's expected results, it is difficult to tell what you want.
Two possible options are multiple CROSS APPLYs, as suggested by Kristen, and recursion followed by a PIVOT.
With the following test data:
CREATE TABLE #t
(
UseageAmt int NOT NULL
,TotalInvQty int NOT NULL
,InvTotalAmt int NOT NULL
);
INSERT INTO #t
VALUES (100, 121, 200);
--select * from #t
- CROSS APPLYs
SELECT *
,TotalInvQty -
(
UseageAmt
+ X1.[0-30 burn off]
+ X2.[31-60 burn off]
+ X3.[61-90 burn off]
+ X4.[91-120 burn off]
+ X5.[121-150 burn off]
+ X6.[151-180 burn off]
) AS [181+ burn off]
FROM #t T
CROSS APPLY (VALUES (CASE WHEN T.UseageAmt < T.InvTotalAmt THEN T.UseageAmt ELSE T.InvTotalAmt END)) X1 ([0-30 burn off])
CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off]) > T.InvTotalAmt THEN T.InvTotalAmt - X1.[0-30 burn off] ELSE T.UseageAmt END)) X2 ([31-60 burn off])
CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off]) > T.InvTotalAmt
THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off]
ELSE T.UseageAmt
END)) X3 ([61-90 burn off])
CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off]) > T.InvTotalAmt
THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off]
ELSE T.UseageAmt
END)) X4 ([91-120 burn off])
CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off] + X4.[91-120 burn off]) > T.InvTotalAmt
THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off] - X4.[91-120 burn off]
ELSE T.UseageAmt
END)) X5 ([121-150 burn off])
CROSS APPLY (VALUES (CASE WHEN (T.UseageAmt + X1.[0-30 burn off] + X2.[31-60 burn off] + X3.[61-90 burn off] + X4.[91-120 burn off] + X5.[121-150 burn off]) > T.InvTotalAmt
THEN T.InvTotalAmt - X1.[0-30 burn off] - X2.[31-60 burn off] - X3.[61-90 burn off] - X4.[91-120 burn off] - X5.[121-150 burn off]
ELSE T.UseageAmt
END)) X6 ([151-180 burn off]);
- Recursion
WITH BurnOffs
AS
(
SELECT B.UseageAmt, B.TotalInvQty, B.InvTotalAmt, 1 AS BLevel
,X.Nbr
,X.Nbr AS NBrTotal
FROM #t B
CROSS APPLY (VALUES (CASE WHEN B.UseageAmt < B.InvTotalAmt THEN B.UseageAmt ELSE B.InvTotalAmt END) ) X (Nbr)
UNION ALL
SELECT B.UseageAmt, B.TotalInvQty, B.InvTotalAmt
,B.BLevel + 1
,X.Nbr
,B.NBrTotal + X.Nbr
FROM BurnOffs B
CROSS APPLY (VALUES (CASE WHEN B.UseageAmt + B.NBrTotal > B.InvTotalAmt THEN B.InvTotalAmt - B.NBrTotal ELSE B.UseageAmt END) ) X (Nbr)
WHERE B.BLevel < 6
)
SELECT UseageAmt, TotalInvQty, InvTotalAmt
,[1] AS [0-30 burn off]
,[2] AS [31-60 burn off]
,[3] AS [61-90 burn off]
,[4] AS [91-120 burn off]
,[5] AS [121-150 burn off]
,[6] AS [151-180 burn off]
,TotalInvQty - (UseageAmt + [1] + [2] + [3] + [4] + [5] + [6]) AS [181+ burn off]
FROM
(
SELECT UseageAmt, TotalInvQty, InvTotalAmt, BLevel, Nbr
FROM BurnOffs
) S
PIVOT
(
MAX(Nbr)
FOR BLevel IN ([1], [2], [3], [4], [5], [6])
) P;
The estimated plans suggest the CROSS APPLYs are more efficient but you should check your actual problem.