question

Matty-4135 avatar image
0 Votes"
Matty-4135 asked ·

Island summing with gaps...

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

[2]: /answers/storage/attachments/75055-image.png

sql-server-transact-sql
image.png (16.3 KiB)
image.png (3.9 KiB)
· 7
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Viorel-1,

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

75445-image.png

Which would mean the following result:

75360-image.png

Thanks,

Matty

0 Votes 0 ·
image.png (32.5 KiB)
image.png (7.7 KiB)

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]
0 Votes 0 ·

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):

75448-image.png

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

Any ideas?

Thanks,

Matty

0 Votes 0 ·
image.png (10.4 KiB)
Show more comments
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ·

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]
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Matty-4135 avatar image
0 Votes"
Matty-4135 answered ·

Thanks Viorel!

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

Cheers,

Matty

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.