TBH, I don't see why the people making the JSON can't make it into a table and populate the table directly.  They even provide a datatype key and they...

Understand that I was speaking only from the SQL Server side... there is no "looping" required to pivot the JSON into a table.

While were on the subject, I wouldn't...

The JSON will always have the same values/pairs as that part is static.

That pairing is still not 100% as I'm taking suggestions provided by Phil/Steves to help improve the...

An alternative

select v1.CompanyName,
       left(v1.CompanyName, sum(iif(ss.ordinal<=1, v.str_len, 0))) _1_word,
       left(v1.CompanyName, sum(iif(ss.ordinal<=2, v.str_len, 0))+1) _2_word,
  ...

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

This mostly borrows your FROM clause except I changed DaysToAdd to be 'int' which the JSON supports (it's not in quotations).  Instead of PIVOT I always use conditional aggregation.  It's...

100% agree with Phil.  Fix the JSON please 🙂  To save myself from toil I used ChatGPT-4 and here's the result

You're right, embedding SQL code in JSON is not a...

• This reply was modified 2 years, 2 months ago by Steve Collins. Reason: Fixed JSON to work as a declared variable in SQL Server

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 2 years, 3 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 2 years, 3 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, 3 months 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...