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 1 years, 5 months 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 1 years, 5 months ago by Steve Collins.
• This reply was modified 1 years, 5 months ago by Steve Collins.
• This reply was modified 1 years, 5 months 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)

Ah ok, this is more correct.  My...

Okay I can easily see how to update a table field after an Insert but do I use the system Inserted when creating a trigger to update a field...

with rn_cte as (
    select l.*, v.gt_one, 
            row_number() over (partition by Number...

The queries so far don't identify the ranges of consecutive "greater than one" rows.  This query uses two ROW_NUMBER functions which both count up across PARTITION BY Number.  To reset...

Is CostKey unique in the CostKeyDeletes table?  If so then making it the CLUSTERED key would be preferable.  To log the process you could add an OUTPUT clause to the...