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

I'm not selling anything.  I'll take it elsewhere tho.  Thanks.

Removed

The goal is to release completed work quicker. Every feature you've completed is inventory. It's work in progress while you wait for QA, packaging, scheduling, and deployment. The absolutely...

.

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

DevOps is a culture, but the culture isn't to write sloppy code faster. It's to release the changes, backwards compatible, quicker. From code commit to release is quick, not...

For a C# data access library that targeted SQL Server maybe 4 or 6 month release could make sense  🙂