Maybe a DELETE statement like this. [Edit: got rid of window function approach and went with COUNT(*) ]

with rn_cte(PatID, ElementIDDate) as (
    select PatID,...

• This reply was modified 4 years, 1 months ago by Steve Collins.

with recent_cte(HorseName, FinishPosition, RaceDate, Rating) as (
    SELECT top 1 with ties HorseName, FinishPosition, RaceDate, Rating
    FROM @RACES
   ...

• This reply was modified 4 years, 1 months ago by Steve Collins.
• This reply was modified 4 years, 1 months ago by Steve Collins.

Following on what Mr. Brian Gale wrote about a DELETE causing a massive gap in the IDENTITY property of a table.  It happens sometimes because things which aren't good sometimes...

Based on the code I would guess your ExceptionLog table is empty because the error handling issues the ROLLBACK before the error values are accessed.  But it's not really guaranteed...

It's a great answer from Jeff which exactly matches the output.  My approach also uses Jeff's "number generator" function dbo.fnTally.  While Jeff "didn't use any of your dates" (because it's...

Maybe like this

if @ItemGroup is null
    Select Itemcode,Itemname,ItemGroup
    from tbl1
    where itemgroup is null;
else
    Select...

Maybe extract the constant and put the correlated subquery in the FROM clause as a CROSS APPLY

declare @vmax_var         ...

Maybe a good way could be an UPDATE statement

update t
set flag = case when v.row_count=1 then 'ANY_EMPTY'
          ...

In the situation where Sum([QTY]) returns the correct the number, AVG(OH) or MAX(OH) ought to return only 1 SKU

At a minimum it's missing a comma at the end of the first line.  Is it necessary to nest SELECT statements within the SELECT list as you've done?  It seems...

Yup, I was one of the 'most people' crowd, thanks for alerting me to that, Jeff. Always good to be aware of these extra possibilities. It was also only...

The DIY way could be to...

Concat_ws was originally in MySQL then the other db's borrowed it iirc.  The big news for me is .NET 6 which I'm really liking a lot because it nicely untangles...

CONCAT_WS() "concat with separator" ignores the nulls

select concat_ws(' | ', t.Col1, t.Col2, t.Col3) [output]
from #t t;

The date could be converted without using string functions.  Also, the WHERE clause might be a more appropriate place to filter the rows.  Maybe something this

declare @UPSBatchInvNot...