The LAG function takes up to 3 parameters:

1) Column name (required)
2) Row offset (optional)
3) Default value (optional)

To find 3 +/- consecutive rows with the same sign maybe...

Redshift is based on an earlier version of Postgres. It was modified to be a columnar database, but it doesn't have many of nice features and functions that were...

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