Json_value() is used to access a single value of up to nvarchar(4000).  Openjson() is used to access arrays and can be nvarchar(max).  In the example json (with array []'s added)...

What you posted is not valid json.  The elements of SecondaryData should be in array brackets.

{
  "odata.id": "0001",
  "MediaId": "0001",
  "SecondaryData": [
  ...

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

Yes there are / will be additional tables where locations and other things will be stored and yes Primary Keys will be introduced as well.

We have literally no choice...

This should come with a warning though.  If there are repeated 'docdata' dates within a partitioning window, then this code will not produce correct results.  If it's possible to implement...

select 
  t.*,
  iif((t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata))<0,0,
  iif((t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata))>t.totalinvoice,
   t.totalinvoice, (t.totalinvoice+sum(t.totalreceived-t.totalinvoice) over...

Does this produce the correct remainder column?

select 
  t.*,
  sum(t.totalreceived-t.totalinvoice) over (partition by receipt_nr order by docdata) remainder
from
  #temptab t

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

Yes, unfortunately it's absolutely not feasible to make changes to the OLTP System where data is inserted initially, it's an 3rd party ISV. All we get is some CSV...

the funny part is: If you look at the OLTP DB and their flow it would appear valid (as in: there seems to be no real checks) and when...

Ok - Joe Celko.  Good luck.

Identity() is a piece of automation that solves a set of tricky problems for you.  Choosing not to use it is tantamount to saying you can do it better.  Well,...

How would I approach checking the OrderNumbers for validity within the steps so an item in "AU" and locked cannot appear at a different site as next record, same...

The S in SCD is "slowly" so I don't see much benefit either.  Also, afaik this feature is not available in SQL DW.

Unless you are required to do this by law, it's more trouble than it's worth.  If you absolutely have to do it, I would use a SEQUENCE and run...

Either by querying your own table or by storing the maximum sequence per day somewhere.  Fyi, this is really not a good way to handle keys and is not recommended. ...

I don't see where you used a GUID either in the original post nor here as a part of the formula.  To be sure, what you did with the...

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