If you want the odd numbered 'rotation' column value(d) rows to be updated from the even numbered rows then LAG would seem the better choice.  First tho, I agree with...

• This reply was modified 1 years, 11 months ago by Steve Collins. Reason: Even/odd were reversed in the first sentence

Maybe something like this.  The OUTER APPLY looks for the next occurrence of [Status] in('collected', 'shipper') for each dropoff and preparation.  Also, LEAD(DateShipped) looks for the next occurrence of [Status]...

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

Maybe get rid of the MAX OVER and just evaluate the Status column

select *, iif([Status] in('Dropoff', 'preparation'), 1, 0) d_or_p
from #ShipperStatusHistory
order by OrderNumber, DateShipper;

Plural.  Each row is a singular instance

Happy Thanksgiving

Based on the written requirements (as interpreted) this query might be helpful.  The comparisons being made across rows are partitioned by (OrderNumber, RelayPoint) and ordered by DateShipper.  To identify...

The date columns are of data type DATE? It's not confirmed by question. For this answer the dates must be stored as DATE.

;with example_cte as (
 ...

• This reply was modified 2 years ago by Steve Collins. Reason: Removed two unnecessary pairs of parentheses

Thank you Jeff.  44.9% or 31%?  Either way there's no implicit type conversion 🙂

Ken McKelvey's solution in the "Pivot with grouping intervals" topic is interesting.  It cross join's a 2...

The top two CTEs could be replaced with only one.  Possibly the bottom two could be combined as well using the "smudge" method?

with 
pvt_cte as (
 ...

Afaik this returns the same output.  The CTEs expand the date intervals, pivot by role name and summarize by day, identify gaps in GROUP BY columns, and create groups using...

From your query results you could CROSS APPLY a row generator created by using the row cardinality from sys.all_columns (which in my test instance is 11,745 rows) and TOP.  The...

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

You could test if the modulo .1 of HRS equals zero

;WITH cte_data AS (
    SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
   ...

Also there's EOMONTH which has an optional second parameter 'offset'.  Regardless of which date/datetime datatype is passed to the EOMONTH function it returns DATE.  Ha, some SSC members were wondering...

All perfect parents have the same thing in common.  No kids

Also my code assumes the RequestNo is UNIQUE NOT NULL and has appropriate sequential values.  The DDL provided says the RequestNo is NULL-able

drop TABLE  if exists #F6000059;
go
CREATE TABLE #F6000059
(
    [RequestNo] [int] NULL,
    [REAN82] [int] NULL,
    [REAN83] [int] NULL,
 ...

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