Something I've always wondered is how is data loaded in a normalized OLTP database? One example that I think about is an ATM transaction because it captures data ranging...

Syntax-wise the CASE expressions are both missing the keyword END.  Also, it's not good to enclose column labels in single quotes because although SSMS and VS have no issues with...

It seems safer to avoid LEAD and LAG because start/stop conditions might repeat within ranges of groups

with 
gap_cte as (
    select *, iif(v.sgn<>lag(v.sgn) over...

On first read it's not very clear.  Having the sample data and expected results makes all the difference.   Not sure about the ORDER BY tho

 with
 Parts_cte(PartId)...

Questions are offered quite acceptable solutions if only some representative sample data is provided.  Make it easy to copy/paste temp table(s) and data.   If it's reduced to "here is the...

Since you're not providing minimally representative data here's a minimally representative query.  One important thing missed in the previous attempt was the 'Bias' indicates over/under vs budget based on +/-1. ...

In general the SIGN function can be useful for "over and under" type situations.  It seems like the question was edited maybe idk.  Anyway, it seems you're looking for the...

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