It appears spacing within certain code blocks have been removed.  It appears to be retroactive across history too.

In my most recent response the spaces in the results code block appear...

"...is it possible to determine the number of years to be able to iterate them and in each turn set the new dates?"

To determine the number of years "to iterate"...

Maybe this

SELECT D.DocumentID AS [Document ID], D.Filename AS [Document Filename], 
       XR.XRefDocument As [Child Document ID], D2.Filename AS [Child Filename], 
...

Maybe it's better to nest PATINDEX functions instead of reversing the string(s)

declare
  @srch             varchar(100)='%[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]%';

/*...

This can be a bit of a pita.  It helps greatly if there is one and only one acceptable date format, like YYYY-MM-DD (padded with zeros).  Another issue is what...

"How can I insert as zero amount if not exist in my working table?"

It seems maybe you're looking to LEFT JOIN the two tables together.   Obviously, you cannot assign attributes...

Maybe something like this.  I added some additional example rows.  It's similar to this other post imo.

insert #temp_docs(BegVal, EndVal, Volume, Prefix, Padding, begnum, endnum) values
('ABC000063738', 'ABC000063738',...

Similar to Jeffrey's answer

with lead_cte as (
    select *, lead(begnum) over (partition by Prefix order by begnum) lead_bn
    from #temp_docs)
select concat(lc.Prefix,...

You could try it like this

DECLARE @CallDateTime DATETIME = '20190331 00:08:13'
DECLARE @Duration NVARCHAR(25) = '01:18:04'

declare @t          datetime=@duration;
select @t+@CallDateTime...

It could be CRUD but only when implemented by Scrum.  Or maybe it's a framework which launches projects miles over the horizon where they explode.

You could try putting CASE logic inside the SUM functions to evaluate your youdatecol

...
ISNULL(SUM(case when youdatecol>=convert(date, getdate()) 
         ...

;with
id_split_person_cte(id, [Owner], ItemNumber, [PersonAttending]) as (
    select sctr.id, sctr.[Owner], splt_person.ItemNumber, splt_person.Item
    from #SplitColumnsToRows sctr
       ...

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

Got any more examples?  It appears there are 2 sets of rules for the 2 different scenarios:

Scenario 1) Owner='' -----> (if [Owner]='' then PersonAttending and Type columns are CROSS JOIN'ed)

Scenario...

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, 4 months ago by Steve Collins.