Good, I couldn't understand why the join was there.

Scott - I would want to log all columns, but only if one or more of the values have changed. An UPDATE...

Thanks. The first one is simple and a good starting point for me.

Why in the below is it necessary to join back to tblOrders?

create trigger tblTriggerAuditRecord on...

As said above, if possible I would store start and end datetime rather than duration. I've had a similar helpdesk case - you store a calculated duration and then someone...

Yes, the two-part method seems to be safer, from Jeff's article. It concludes with recommending an anchor but I couldn't find any examples of this earlier in the article so...

Got it working with a version of the Quirky Update, so thanks to Jeff Moden.

Changed it to a temp table rather than table variable and ThisDate is a clustered index....

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()...