Island summing with gaps...

Question

Island summing with gaps...

Matty 41

Hi Team,

Suppose I have a table that looks as follows:

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:

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

Matty 41 Reputation points

2021-03-08T13:26:30.073+00:00

Hi Viorel-1,

Can this solution be adapted based on a further column, like this?

Which would mean the following result:

Thanks,

Matty

Viorel 121.8K

This seems to work:

;
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 Product, [Date], (select sum([Value]) from MyTable where [Date] between e.sd and e.[Date]) [Value]
from E2 e
where [Value] is not null and [next] is null
order by [Date]

Matty 41 Reputation points

2021-03-08T17:31:07.707+00:00

Hi again,

Thanks for responding, but that doesn't quite give me what I need.

Here's what it's returning (for me at least):

Green records are correct; orange ones not. The problem appears to be where Products have date ranges that overlap.

Any ideas?

Thanks,

Matty

Viorel 121.8K

If the data should be grouped by products, then check this query:

;
with E1 as
(
    select *, 
        lag([Value]) over (partition by Product order by [Date]) as [prev],
        lead([Value]) over (partition by Product 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 Product, [Date], (select sum([Value]) from MyTable where [Date] between e.sd and e.[Date]) [Value]
from E2 e
where [Value] is not null and [next] is null
order by Product, [Date]

Matty 41 Reputation points

2021-03-08T19:00:25.02+00:00

Thanks for that. It's getting closer, but there's still an issue if two Products share the same Date.

Let's say we add 1000 to Pear on the 05/01/2021, we then get this result:

Where Apple then shows 1010 on the 06/01/2021 when it should be 15, and Pear shows 1005 on the 05/01/2021 when it should show 1000.

Any ideas?

Thanks,

Matty

Viorel 121.8K

Try this fix:

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

Matty 41 Reputation points

2021-03-08T19:54:29.277+00:00

Working perfectly now. Thanks for your help and patience.

Cheers,

Matty

Accepted answer

1 additional answer

Your answer

Matty 41 Reputation points

2021-03-08T13:26:30.073+00:00

Hi Viorel-1,

Can this solution be adapted based on a further column, like this?

Which would mean the following result:

Thanks,

Matty
Viorel 121.8K Reputation points

2021-03-08T17:09:52.357+00:00

This seems to work:

; 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 Product, [Date], (select sum([Value]) from MyTable where [Date] between e.sd and e.[Date]) [Value] from E2 e where [Value] is not null and [next] is null order by [Date]
Matty 41 Reputation points

2021-03-08T17:31:07.707+00:00

Hi again,

Thanks for responding, but that doesn't quite give me what I need.

Here's what it's returning (for me at least):

Green records are correct; orange ones not. The problem appears to be where Products have date ranges that overlap.

Any ideas?

Thanks,

Matty
Viorel 121.8K Reputation points

2021-03-08T17:55:15.24+00:00

If the data should be grouped by products, then check this query:

; with E1 as ( select *, lag([Value]) over (partition by Product order by [Date]) as [prev], lead([Value]) over (partition by Product 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 Product, [Date], (select sum([Value]) from MyTable where [Date] between e.sd and e.[Date]) [Value] from E2 e where [Value] is not null and [next] is null order by Product, [Date]
Matty 41 Reputation points

2021-03-08T19:00:25.02+00:00

Thanks for that. It's getting closer, but there's still an issue if two Products share the same Date.

Let's say we add 1000 to Pear on the 05/01/2021, we then get this result:

Where Apple then shows 1010 on the 06/01/2021 when it should be 15, and Pear shows 1005 on the 05/01/2021 when it should show 1000.

Any ideas?

Thanks,

Matty
Viorel 121.8K Reputation points

2021-03-08T19:06:59.523+00:00

Try this fix:

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

2021-03-08T19:54:29.277+00:00

Working perfectly now. Thanks for your help and patience.

Cheers,

Matty

Answer 1

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]

Answer 2

Matty 41

Thanks Viorel!

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

Cheers,

Matty

Share via

Island summing with gaps...

1 additional answer

Your answer