The entire plural column is 'no' including temporal tables.  If tables are collections of rows then they should be plural, no?  If the table name is 'orders' then one row...

Just...

Just a few...

An alternative to nested sets is nested json arrays.  Here it is using Jeff's (small) Employee table (renamed to dbo.Test_Employee)

drop table if exists dbo.Test_Employee;
go
CREATE TABLE dbo.Test_Employee
 ...

To see if DesNorton's solution worked (it does) and to see if the interval could be created in Sql 2012 I tried with this sql although I don't have any...

By the way, I did have a script published here on SSC that includes an example of nested json arrays.  Please see:

https://www.sqlservercentral.com/scripts/function-and-queries-to-convert-hierarchical-adjacency-to-nested-json-arrays

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, 12 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, 12 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.