Suppose there's an ordinal column called 'Ord'.  The WHERE clause makes sure the "first" row is a "root" ElementID.  The OUTER APPLY selects the ElementID to substitute if the row...

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

Say I have a table that allows nulls on some columns. As records are added, some columns are filled only say 20% of the time. is there an easy...

It doesn't seem possible without additional information.  In your FIRST_VALUE function the PARTITION BY patid and ORDER BY elementiddate doesn't seem to deterministically define the order of the rows where...

delete p
from #pat p
where p.ElementID='B'
      and exists(select 1
              ...

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