I actually set the table option to have "large data types (LOBs)" be stored "out of row" (to prevent "trapped short rows" and other page density issues), which is...

Bump.  Maybe the prior posting was never visible

Error severity 20-25 are system errors so I wouldn't want to turn off logging and consider that the solution :).  There's a  packet size system setting for which there's a...

I can request that to happen, if it achieves the end result and can simplify the process. Its something we can work towards.

If I go down this path, I'll...

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, 8 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, 8 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, 9 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, 9 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...