Assume there are 2 tables, 1 is payment and the other is deduction. Now the following query which I got from somewhere gives me following result

DeductionID DeductionAmount PaymentID   PaymentAmount   DeductionBalance    PaymentBalance
1   1000.00 1   500.00  500.00  0.00
1   1000.00 2   550.00  0.00    50.00
2   150.00  2   550.00  100.00  0.00
2   150.00  3   100.00  0.00    0.00

This all seems good but I need amounts which have been deducted i.e.

DeductionID DeductionAmount PaymentID   PaymentAmount   DeductionBalance    PaymentBalance
1   1000.00 1   500.00  500.00  500
1   1000.00 2   550.00  0.00    500
2   150.00  2   550.00  100.00  50
2   150.00  3   100.00  0.00    100

Following is the query

DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);

INSERT @Deductions (DeductionAmount) VALUES (1000),(150);

DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);

INSERT @Payments (PaymentAmount) VALUES (500),(550),(100);

-- Preprocessing

IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;

SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]

INTO #Deductions

FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;

CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);

IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;

SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]

INTO #Payments

FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;

CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);

-- Generate result set

-- Note that Deduction 2 is covered by Payments 3 AND 4.

-- Please check your figures in your expected result set

SELECT

    DeductionID, 
    DeductionAmount, --d.[from], d.[to],
    PaymentID, 
    PaymentAmount, --p.[from], p.[to],
DeductionBalance = CASE WHEN d.[to] > p.[to] THEN 
                        d.[to] - p.[to] 
                    ELSE 
                        0 
                    END,

PaymentBalance = CASE WHEN p.[to] > d.[to] THEN 
                    p.[to] - d.[to] 
                WHEN d.[to] IS NULL THEN 
                    PaymentAmount 
                ELSE 0 END

FROM #Deductions d

FULL OUTER JOIN #Payments p

ON p.[from] < d.[to] AND p.[to] > d.[from]

ORDER BY ISNULL(d.DeductionID, 1000000), p.PaymentID;

Thanks

I tried to do it using available fields but failed to get amount.