I was thinking that for most purposes, an explicit transaction would suffice.


BEGIN TRAN

DELETE statement

INSERT statement

COMMIT TRAN


Drew

You've been around long enough to know to provide sample data and expected results.

Drew

Isn't it as simple as a CASE expression?

December 19, 2017 at 9:16 am

#1972718

First, when working with dates, store them as dates.  Part of the reason that you're having problems is...

You just use both columns in your PARTITION BY clause.

Drew

It requires two different ROW_NUMBER()s.

;
WITH StatusGroups AS
(
    SELECT *
    ,    Grp = ROW_NUMBER() OVER(PARTITION BY EID ORDER BY StartDate, Stat) - ROW_NUMBER() OVER(PARTITION BY EID,...