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

Answer 2

Check this out, The idea is to use the accumulated operation and then subtracted the needed value as below:

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


INSERT INTO @TempTable (CustID, RODate, Operation)
VALUES  (10, DATEADD(MONTH, -2, GETDATE()), 5),
        (10, DATEADD(MONTH, -1, GETDATE()), 6),
        (10, GETDATE(), 7)

        select * from @TempTable


        Declare @noOfOperation int =8
        Declare @noOfMonths  int =3

        Declare @StartDate date,@DateEnd date,@avNoOfOperation int

        --get the range you are working for
        select 
         @StartDate=cast(cast(year(dateadd(Month,-@noOfMonths+1,getdate())) as varchar(4))+'-'+cast(Month(dateadd(Month,-@noOfMonths+1,getdate())) as varchar(2))+'-01' as date) 
         ,@DateEnd=dateadd(day,-1,cast(cast(year(getdate()) as varchar(4))+'-'+cast(Month(getdate()) as varchar(2))+'-01' as date)) ;

        --get the total of avaliable operation, for validating before subtracting
        select @avNoOfOperation=sum(t.Operation) from @TempTable t where cast(t.RODate as date) between @StartDate and @DateEnd

        --review the variables if needed
        --select @StartDate [@StartDate],@DateEnd [@DateEnd],@avNoOfOperation [@avNoOfOperation]

        if(@avNoOfOperation>=@noOfOperation and @noOfOperation>0)
        begin
            --only here we can start subtracting
                ;with DataIncluded as (
                    select *,@noOfOperation [noOfOperation],sum(Operation) over (order by RODate) [AcOp] from @TempTable t where cast(t.RODate as date) between @StartDate and @DateEnd
                ),SubtractDataSet as (
                    select  *,AcOp-@noOfOperation [leftOp],
                    case when (AcOp-@noOfOperation)<=0 then 0 else 
                        case when (AcOp-@noOfOperation)<Operation then AcOp-@noOfOperation else Operation end  end [UpOp]
                    from DataIncluded 
                )
                Update @TempTable 
                    set A.Operation=B.[UpOp] 
                    From @TempTable A
                        inner join SubtractDataSet B on A.TempID=B.TempID

        end

select * from @TempTable

Note: Im not using the current month so my output is different then the one you suggested. if the inputs was as follow:

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

The output would be :-

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