I'm trying to return a decremental column in SQL SELECT Statement using LAG function. The result until the 2nd row is the expected, but for 3rd the result is different.

The original dataset:

The expected result is more one column with Remaining decremental (Available - Consumed):

I tried with the query:

select 
    AvailableId
    ,Date
    ,Hour
    ,Consumed
    ,Available
    ,case 
            when row_number() over(partition by AvailableId order by Date asc, Hour asc) = 1 then Available
            when row_number() over(partition by AvailableId order by Date asc, Hour asc) = 2 then (Available - lag( Consumed ) over(partition by AvailableId order by Date asc, Hour asc))
            else ((Available- lag( Consumed ) over(partition by AvailableId order by Date asc, Hour asc)) - lag( Consumed ) over(partition by AvailableId order by Date asc, Hour asc))
    end as Remaining
from 
    #temp

But the result was:

After the Second Row the decrement not work (46 - 18), this works fine only the AvailableId have two lines. Someone knows how to return the expected result for 3rd, 4th, until N rows in this case?

The DDL:

create table #temp(
    [AvailableId] int not null
    ,[Date] date not null
    ,[Hour] int not null
    ,[Consumed] int null
    ,[Available] int null
)
go

insert into #temp values
    (158632,'2022-03-05',08, 4, 50),
    (158632,'2022-03-05',09, 18, 50),
    (158632,'2022-03-05',10, 28, 50)
go

CodePudding user response：

I follow the tip of #Larnu and I could return the expected result using cumulative sum:

with cte as(
    select 
        t1.AvailableId
        ,t1.Date
        ,t1.Hour
        ,t1.Consumed
        ,t1.Available
        ,sum(t1.Consumed) over(partition by t1.AvailableId order by t1.Date asc, t1.Hour asc) as CumSum
    from 
        #temp as t1
)
select 
    t2.AvailableId
    ,t2.Date
    ,t2.Hour
    ,t2.Consumed
    ,t2.Available
    ,case
        when lag(t2.CumSum) over(partition by t2.AvailableId order by t2.Date asc, t2.Hour asc) is null then t2.Available
        else t2.Available - lag(t2.CumSum) over(partition by t2.AvailableId order by t2.Date asc, t2.Hour asc)
    end as Consumed
from
    cte as t2

Thank you @Larnu!