with stub_cte as (
    select BName, StartDate, StartTime, 
           lag(StartDate) over (partition by v.stub...

The @dateChoice variable is passed in from a dropdown box and the WHERE clauses between the two queries are different.  Syntax-wise you could try IF @dateChoice = 'DOS' SELECT ... ...

The only relational operator which seems required is a partial CROSS JOIN of #tmp (WHERE 'parent_id' IS NULL) to #tmp (WHERE 'parent_id' IS NOT NULL) ON 'uniqueid'.  Then it's a...

In your JSON the outermost delimiter is an array bracket which means it's an anonymous array.  To reference the only record in the array you could provide the 'path' to...

In the function JSON_VALUE 'path' is a required parameter and in the tvf OPENJSON 'path' is optional. The implementation of 'json path' in SQL Server is a limited subset of...

Where's your sample data?  A calendar table determines the workdays?  This is asking a bit.  Using AVG OVER can only accept literal numbers in the RANGE PRECEDING and afaik it...

You can combine the two deletes into a single delete.

WITH Combine_Colors AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY p.Airplane ORDER BY p.[Priority]) AS rn
FROM #Priority AS p
WHERE p.Color...

Sorry, sorry my apologies.  I didn't catch the inconsistency.  The green arrows in the image you posted only point up!  Afaik that's the part I missed.  Once there's an accepted...

Instead of 3 statements: "UPDATE Color, DELETE Color='White', UPDATE Priority", these 2 statements seem equivalent and simpler: "DELETE Color='White', UPDATE Color, Priority"

NOTE: As per the post below the code...

• This reply was modified 3 months, 1 week ago by  Steve Collins.

Happy Holidays to SSC!  Nicely done Phil.  Hmm... an issue which could potentially screw up the solution is the lack of constraint(s) on the input table.  Why are all of...

• This reply was modified 3 months, 1 week ago by  Steve Collins.
• This reply was modified 3 months, 1 week ago by  Steve Collins.
• This reply was modified 3 months, 1 week ago by  Steve Collins.

That's a nice looking query Ken.  The OP's query used "SELECT ... INTO #TEMP1" which could be a bottleneck to a larger process imo.  Of course, it's not possible to...

Scroll up a few posts and see Ken McKelvey's reply.  Once t_stamp has been converted to...

Well, I admit I've never used NOLOCK in a query.  There hasn't been any project where NOLOCK is/was clearly required.  The article doesn't actually put forward any performance metrics or...

Yep, t_stamp stored as BIGINT could be converted to DATETIME2(3). ...

Or the paradox is an enigma wrapped in a riddle.  The article is based on a musing which is factually unconfirmed.  Or maybe I missed the convincing part(s)