Island summing with gaps...

Matty 41 Reputation points
2021-03-06T18:10:02.957+00:00

Hi Team,

Suppose I have a table that looks as follows:

![75019-image.png]2

I want to use the NULLs (blank cells here) as a means of identifying the 'islands' to sum as well as using the last date for each 'island' as the date assigned to the summed value. The output would be as follows:

75062-image.png

Hope what I'm trying to do makes sense. I've been playing around with row_number / over / partition by, but can't get the logic quite right.

Thanks,

Matty

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Viorel 114.5K Reputation points
    2021-03-06T18:47:02.173+00:00

    Check one of possible solutions:

    ;
    with E1 as
    (
        select *, 
            lag([Value]) over (order by [Date]) as [prev],
            lead([Value]) over (order by [Date]) as [next]
        from MyTable
    ),
    E2 as
    (
        select *,
            (select max([Date]) from E1 where [prev] is null and [Value] is not null and [Date] <= e.[Date]) sd
        from E1 e
    )
    select [Date],
        (select sum([Value]) from MyTable where [Date] between sd and e.[Date]) [Value]
    from E2 e
    where [Value] is not null and [next] is null
    order by [Date]
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Matty 41 Reputation points
    2021-03-06T18:55:00.38+00:00

    Thanks Viorel!

    Not used the LAG and LEAD functions before - I will investigate!

    Cheers,

    Matty

    0 comments No comments