How to Sum (MAX values) from different value groups in same column SQL Server-CodePudding

I have a table like this:

Date	Consec_Days
2015-01-01	1
2015-01-03	1
2015-01-06	1
2015-01-07	2
2015-01-09	1
2015-01-12	1
2015-01-13	2
2015-01-14	3
2015-01-17	1

I need to Sum the max value (days) for each of the consecutive groupings where Consec_Days are > 1. So the correct result would be 5 days.

CodePudding user response：

This is a type of gaps-and-islands problem.

There are many solutions, here is one simple one

Get the start points of each group using LAG
Calculate a grouping ID using a windowed conditional count
Group by that ID and take the highest sum

WITH StartPoints AS (
    SELECT *,
      IsStart = CASE WHEN LAG(Consec_Days) OVER (ORDER BY Date) = 1 THEN 1 END
    FROM YourTable t
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (ORDER BY Date)
    FROM StartPoints
    WHERE Consec_Days > 1
)
SELECT TOP (1)
  SUM(Consec_Days)
FROM Groupings
GROUP BY
  GroupId
ORDER BY
  SUM(Consec_Days) DESC;

db<>fiddle

CodePudding user response：

with cte as (
select  Consec_Days,
    coalesce(lead(Consec_Days) over (order by Date), 1) as next
from    YourTable
)
select sum(Consec_Days)
from cte
where Consec_Days <> 1 and next = 1

db<>fiddle