SQL deduct some numbers from rows conditionally

Question

I have a table (showing temp table)

CREATE TABLE #TempTable
(
    TempID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustID INT NOT NULL,
    RODate DATE NULL,
    Operation INT NULL
);

This table has this sample data:

INSERT INTO #TempTable (CustID, RODate, Operation)
VALUES  (10, DATEADD(MONT, -2, GETDATE()), 2),
        (10, DATEADD(MONT, -1, GETDATE()), 3),
        (10, GETDATE(), 5)

So table have below data

TempID  CustID                  RODate          Operation
-----------------------------------------------------------
1         10                    2019-03-17          2
2         10                    2019-04-17          3
3         10                    2019-05-17          5

Requirement is I will get one integer variable in parameter which is @noOfOperation, let's say its value is 10

I will also get no of months in parameter, let's say it's 3

I have to query the table to return data for last 3 months only (excluding current month (date asc)

Then I have to deduct @noOfOperation from the table and update.

Deduction will be based on availability in operation column.

For example: in this case first we will deduct from 2019-03-17

10 - 2 = 8 (operation column for this row  becomes 0)

Next we will deduct from 2019-04-17

8 - 3 = 5 (operation column for this row  becomes 0)

Similarly for 2019-05-17

5-5 = 0 (operation column for this row  becomes 0)

I have to check if @noOfOperation is less than or more than the number of operation of individual months then do the above accordingly

Answer 1

--Change the value of @OperationsToBeDeducted, to see different results

declare @OperationsToBeDeducted int
declare @OperationsRemaining int
declare @RODate date

set @OperationsToBeDeducted = 4

declare @TempID int
set @TempID = 1

DROP TABLE IF EXISTS #TempOperation
create table #TempOperation
            (
                TempID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
                CustID INT NOT NULL,
                RODate DATE NULL,
                Operation INT NULL
            );

insert into #TempOperation (CustID,RODate,Operation)
values
(10,DATEADD(month, -3, getdate()),2),
(10,DATEADD(month, -2, getdate()), 2),
(10,DATEADD(month, -1, getdate()),3)


DROP TABLE IF EXISTS #TempOperation2
create table #TempOperation2
            (
                TempID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
                CustID INT NOT NULL,
                RODate DATE NULL,
                Operation INT NULL
            );
insert into #TempOperation2 select CustID,RODate, Operation from #TempOperation

select * from #TempOperation2 order by RODate asc

declare @maxID int;
select @maxID = max(TempID) from #TempOperation2

while (@TempID <= @maxID)
begin

    set @OperationsRemaining = 0

    select @OperationsRemaining = Operation, @RODate = RODate from #TempOperation2 where TempID = @TempID

    if(@OperationsToBeDeducted is not null and @OperationsRemaining is not null and 
    @OperationsRemaining > 0 and @OperationsRemaining > @OperationsToBeDeducted)
    begin
        update #TempOperation set Operation = @OperationsRemaining - @OperationsToBeDeducted where TempID = @TempID
        set @OperationsToBeDeducted = 0
    end
    else if(@OperationsToBeDeducted is not null and @OperationsRemaining is not null and 
    @OperationsRemaining > 0 and @OperationsRemaining <= @OperationsToBeDeducted)
    begin
        set @OperationsToBeDeducted = @OperationsToBeDeducted - @OperationsRemaining
        update #TempOperation set Operation = @OperationsRemaining - @OperationsRemaining where TempID = @TempID

    end
    SET @TempID  = @TempID  + 1
end
select * from #TempOperation order by RODate asc

DROP TABLE #TempOperation
DROP TABLE #TempOperation2