It seems the data is ordinally JOIN'ed between PersonAttending and Type and CROSS JOIN'ed between PersonAttending and Owner.  To do the ordinal splitting this uses dbo.DelimitedSplit8K_LEAD.  Ordinal splitters and specifically...

Similar to the accepted solution but without the common table expression.  It appears Jeff began to code it one way and then switched

select max(case when sp2.ItemNumber=1 then...

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

The additional rows were generated using dbo.fnTally

select case when fn.n=0 then k.CDMLOCID else '"' end CDMLOCID,
       case when fn.n=0 then...

Hmmm it's hard to be certain without testing.   I've had issues with inserts of multiple rows with the same system datetime into System Versioned tables.  It's the "simultaneous" part it...

If I'm understanding this could this be done in the WHERE clause with a bunch of OR conditions.

select * 
From Master as pom
WHERE Pom.UserID = @userid
 ...

Have a look at the Docs for SUBSTRING

SUBSTRING ( expression ,start , length )

When the CASE statement is evaluated as TRUE, because you're adding +1 to the length LEN...

Yes the repository is the better place for the code.  There are going to be many changes and now this thread seems incomplete.  Maybe it makes sense to start over...

Hi Steve Jones, when these posts were consolidated from what were originally 7 separate threads it appears a post(s)? containing the DDL and explanation for having a System Version'ed edition...

It's been a few weeks since there's been an update.  We've had some work upheaval and also my son's 6th Birthday party and the Memorial Day holiday.  Workwise I've been...

The function could be included in the SELECT list

select lcd.LoggerDownloadTableName,
       smalldatetimefromparts(substring(lcd.LoggerDownloadTableName, 18, 4),
        ...

You could try SMALLDATETIMEFROMPARTS

select v.input, 
       smalldatetimefromparts(substring(v.input, 18, 4),
            ...

The least friction way imo seems to be to split each character using fnTally and convert to ASCII values.  Then re-aggregate the string.  Something like this (with extra non-[a-z][0-9]) characters...

You could try something like this

select ph.PolId, 
       case when isnull(pa_est.est_count, 0)>0 
        ...

Sure, the function is described in this really nice article.   This is the code I use

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC:...

This query is really close but maybe there's an inconsistency issue with the 'tab1_result' query?

with tab1 as
(
    select 101 as id, 'abc' as area,'2021-01-07'...