Thanks Ken, for the confirmation that it cannot be done through windowing functions. This post is actually a follow-up to an earlier one I put up - Is loop necessary?...

You have the input in @T.

The output required is:

'2021-01-01', 100, null, 1

'2021-01-06', 125, 5.00, 1

'2021-01-08', 325, 100.0, 0

'2021-01-16', 155, 3.00, 1

I don't know how I can make it any clearer,...

The first three rows output from my attempted query are what I expect.

The last row (2021-01-16) I want to be Usage = 3 and Result = 1. Currently, it is...

The scenario is:

Result (Pass or Fail) is a function of Distance, where Distance = N - (N of previous latest Pass)

How can you work out Result for row N without...

Many thanks to both of you!

I'd got bogged down in trying to use row_number and dense_rank, and didn't think about using lag.

No, it's well enough defined.

Thanks vliet. It is elegant but having to use a literal value in the windowing frame is a big limitation.

The islands method above also uses six windowing functions - two...

In my previous attempts, I don't think I can guarantee that RowGroup is unique across the two 'paths', and so could get incorrect result when combined. Would depend on the...

And without the UNION ALL.

declare @AtLeastCount int = 3

;with T as
(
select *, 
RowNum0 = case when Val = 0 then row_number() over(order by ID) else null end,
RowNum1...

My solution, no claim to originality.

declare @AtLeastCount int = 3

;with T as
(
select *, RowNum = row_number() over(order by ID)
from @T
),
T1 as
(
select *, RowGroup = RowNum - row_number()...

Thanks to all for the solutions!

On a quick look, sparked enough ideas for me to try and (hopefully) write a solution - don't want to be beaten by it. I...

ZZartin thanks, that works. What if it was 'at least 4 1s' (would need new test data). Ideally the number would be variable, could that be achieved.

The @R is just to get into a table what the result should be so that it can be easily read. It will play no part in any solution.

I did...

select 'After', NULL, NULL

is not required in the result.

Yes, I wasn't arguing for natural over surrogate keys, and tend to have both if possible. An ID for simplicity of joins and a unique index on other column(s). Hope...