That's freaking amazing.  It's awesome to see real in depth analysis.  Thank you for doing this.

Shifting gears a bit, since the Nested Sets method has most of...

That's freaking amazing.  It's awesome to see real in depth analysis.  Thank you for doing this.

Shifting gears a bit, since the Nested Sets method has most of the goodies...

Those columns are not needed, I think.  At the end of the 2nd article (allow me to quote you some Jeff Moden) it says:

Last but certainly not least, if you...

Can you use the...

Imo expressing the recursive "downlines" or "sort path" as nested json makes the information much more usefully accessible.  You can check out an example at:

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

What is the DBA world consensus on using this to implement databases for an application? An application developer presented this as the "future of EF" but I do not...

It's conditional counting.  Sql is terrible at this.

Yeah I noticed that too.  My next attempt would be to use CROSS APPLY containing IIF function(s) (as many as necessary) and pass in a ton of variables.

This appears to work but it's not simple.  It defines the anchor records as having the minimum ArrivedDate where the doc_row_num<=2 and doc_row_num<= doc_count.  Then it inserts additional rows calculating...

/* without STRING_AGG */
;with
ErrData1(tran_no, ref_no, ref_tamt) as (
    select t1.tran_no, t1.ref_no, t1.ref_tamt
    from @tran_dtl1 t1
    where t1.ref_tamt >...

Maybe STRING_AGG instead of COALESCE?

Well I have no sample data so that was a stab in the dark.  Makes sense though!  Maybe more iif's

select
  concat(iif((columnproperty(object_id('dbo.TempparsedHeaderCSVData'),'Footnote','ColumnId') IS NOT NULL) and 
             (columnproperty(object_id('dbo.TempparsedHeaderCSVData'),'Footnote2','ColumnId') IS NULL), 
   ...

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

drop table if exists #anchorj;
create table #anchorj(
  UserId        varchar(10),
  SalesDate     date,
  SalesAmount ...

Sorry, I was happy because my project worked.  I'm not trying to sell anything.  You won't miss this post so it's gone.