This is what I came out with.

No idea if it will perform well. So many iterations and changes as I got to understand the intracacies of your requirement.

I suspect it will be better as it removes redundant distincts and has less windowing, but I'm still grouping a lot so who knows?

;WITH CTE_PROD AS (

SELECT

ROW_NUMBER() OVER (PARTITION BY MASTERID ORDER BY RECORDDATE ASC) rownum,

CASE WHEN HOURSFLOWED >= 18 THEN 1 ELSE 0 END IncludeCurrent

,MasterID,recorddate

FROM PROD A

),

gaps AS (

SELECT

rownum,

MASTERID

FROM CTE_PROD

WHERE IncludeCurrent = 0

),

nearestgapbelow AS (

SELECT

c.rownum,

c.MASTERID,

C.RECORDDATE,

COALESCE(MAX(g.rownum),0) closestGap

FROM CTE_PROD c

LEFT JOIN gaps g

ON c.MASTERID = g.MASTERID

AND c.rownum > g.rownum

WHERE c.IncludeCurrent = 1

GROUP BY c.rownum,c.MASTERID,c.RECORDDATE

),

islands AS (

SELECT c.rownum,c.MasterID, c.RECORDDATE ceilingDate,f.RECORDDATE floorDate, DATEDIFF(day,f.recorddate,c.recorddate)+1 DayRange

FROM nearestgapbelow c

JOIN nearestgapbelow f

ON c.MASTERID=f.MASTERID

AND c.rownum = f.rownum + 6

AND c.closestGap = f.closestGap

),

closestisland AS (

SELECT

d.MASTERID,

d.RECORDDATE,

MAX(i.rownum) rownum

FROM DOWN d

LEFT JOIN islands i

ON d.MASTERID = i.MASTERID

AND d.RECORDDATE > i.ceilingDate

GROUP BY d.MASTERID, d.RECORDDATE

)

SELECT

ci.MASTERID,

ci.RECORDDATE,

i.floorDate,

i.CeilingDate,

i.DayRange,

AVG(p.HOURSFLOWED) AVG_FLOW_HOURS,

SUM(p.HOURSFLOWED) SUM_FLOW_HOURS

FROM closestisland ci

LEFT JOIN islands i

ON ci.MASTERID = i.MASTERID

AND ci.rownum = i.rownum

LEFT JOIN PROD p

ON i.MASTERID = p.MASTERID

AND p.RECORDDATE BETWEEN i.floorDate AND i.ceilingDate

GROUP BY ci.MASTERID,ci.RECORDDATE,i.floorDate,i.ceilingDate,i.DayRange

ORDER BY ci.MASTERID,ci.RECORDDATE DESC