The 2nd item in the select list is not included in the GROUP BY clause.  Shouldn't it be?  The tables which are nested inside the STUFF function join to the...

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

select substring(@text, len(@text)-11, 8);

You marked your own answer as the answer?  Nicely done!

declare
  @text               ...

• This reply was modified 6 years, 7 months ago by Steve Collins. Reason: now looks for reverse('_files')

Nicely done Mark Cowne.  My attempt avoids the inequality on date (which makes me nervous) but is otherwise the same.

with
t_rn_cte(person_from, person_to, kind, pctg_new, eff_date, row_num) as (
...

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

Here's an article from SSC:

https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file

If it were me I'd use BCP, the Bulk Copy Program that comes with SQL Server.

What happens if you add TOP(1)?  Will any row convert or does every row fail?

drop function if exists dbo.test_name_chopper;
go
create function dbo.test_name_chopper(
  @PREFX VARCHAR(10),
  @DELIM VARCHAR(30),
  @AD_STR VARCHAR(500))
returns table as 
return
select
  IIF(PATINDEX(@DELIM,@AD_STR collate Latin1_General_CS_AS)<=0, 
   ...

Well in my opinion just looking for a comma is risky.  Trying to comma parse the entire string multiplies the risk!  So that seems unnecessary imo.  Here's a safer way...

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

Give it a try and see!  🙂  Yes it should work like:

select
  substring(@test, 4, charindex(N',OU=' collate Latin1_General_CS_AS, st.some_column, 4)-4) some_name
from
  some_table st;

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

The safest way to do this imo is to look for the first occurrence of ',OU=' in a case-sensitive way.

declare @testnvarchar(max)=N'CN=Some Person,OU=Sales,OU=Some Company - SK,OU=Companies - Some...

Maybe successively cross apply with partial joins?  Something like:

with
matrix_1(month_nbr, x_group, [1], [2], [3]) as (
    select 1, 'grp a', 1, 1, 1
  ...

Yes, union all works well.  Or you could insert into @Temptable twice.  It's not clear whether the information in the inserted 'Invoice' should contain all of "same informations (ID,DKey,SKey)..." because...

The whole retry loop could be removed in my opinion.  This is a basic upsert.  The variables used for flow of control are deterministic so the retry is not really...

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

with
hotel_cte(customer_id, type_of_event, event_dt) as (
select 1, 'check in', '2019-12-30'
union all
select 1, 'check out', '2020-01-14'
union all
select 2, 'check in', '2020-01-14'
union all
select 2, 'check out', '2020-01-22'
union all
select 3, 'check...

Final answer, shamelessly borrowing from Jonathan, still without CROSS APPLY.  Also, Jonathan's answer with sample data cte included (copy/paste/run).

/* borrowed */
with
LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
select 12, 10,...