Hmmm... that's odd.  Maybe we're both right until the OP clarifies?!?

When the OP first posted: Input

SELECT 10,'03/22/2020','12/31/9999'

Output:

10 '03/22/2020' '03/31/2020'

10 '04/01/2020' '12/31/9999'

Then the second post breaks down the same data differently...

Aaah that was true before I borrowed the charindex section of Phil's code.

drop function if exists dbo.fnTIDprefix_Expand;
go
create function dbo.fnTIDprefix_Expand(
  @TIDprefix      ...

Jason A. Long the "s3 string" characters which appear after the brackets do not appear in any of the OP's example rows.  Imo that's a made up requirement which most...

When I switch the datediff metric to nanoseconds and run the script over and over again the tally and cte are equal at 0 ns up to about 500 rows. ...

This script prints start/stop timestamps before/after running 2 queries.  Query 1 counts rows generated by the tally function.  Query 2 uses a recursive CTE.  With n=10000, Q1 takes 0 ms. ...

Afaik the OP didn't state the end dates couldn't be in the future.  It seems safer to test against '9999-12-31'.  Still WITH SCHEMABINDING so no physical table.  Here's using the...

Ok, if the end date is less than the deceased date, then use the end date, else use the deceased date.

select ds.*, fsm.*
from
  #date_split ds
 cross...

It's been refactored into an fnSplitMonth function WITH SCHEMABINDING.  Then I did what Sergiy suggested 🙂

/* updated */
drop table if exists #date_split;
go
Create Table #date_split(
  ID ...

Aha ok yes afaik the cache is clear-able on local instances.  I work mostly on Azure SQL these days and the DBCC statements to clear cache don't apply/work (maybe yet?). ...

Ok so is the execution cache cleared of the tally function between each execution?  Or according to what timeout period or ??

... Your solution is much more cpu heavy if the statement is only rarely executed and needs to be compiled every time. I think that the design will be...

drop table if exists #date_split;
go
Create Table #date_split
(
ID             INT,
StartDate      DATE,
EndDate ...

That was the correct clarification 🙂

Imo that does not appear to be an infallible method.  Would you agree?

In 2016+ it could be done like this

drop function if exists dbo.fnCommaOnce;
go
create function dbo.fnCommaOnce(
  @comma_str           ...

• This reply was modified 5 years, 4 months ago by Steve Collins. Reason: Added within group order by