I'd urge you to add a bit flag to the PROD table to indicate whether a given row has 7 consecutive valid flow values or not, rather than recomputing every time across ~4.5M rows.

If somehow historical PROD table data gets deleted/added/changed, you'd need a trigger to recompute the bit each time when a row(s) were DELETEd, INSERTed or UPDATEd (naturally you'd have to recompute a range of rows around the directly affected row(s)).

Then I think a simple OUTER APPLY could quickly give you the list you need, esp. if the tables are clustered by date -- or (masterid, date) if applicable -- as would seem logical based on table usage so far.