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'...

In the code below 'start_cte' creates a column called 'start_ind' which is equal to 1 when the AVAILABLE column has changed from 0 to 1 else it is 0.  To...

• This reply was modified 4 years, 6 months ago by Steve Collins. Reason: Changed stop_ind to start_ind to make clearer

Because they're different tools for different jobs.  JSON or XML are supposed to be versatile(unlike older mainframe type hierarchies) and capable of being transmitted and consumed by a broad...