drop table if exists dbo.TestBatch;
go
create table dbo.TestBatch(
  [BatchNum] [nvarchar](10) NULL,
  [ItemName] [nvarchar](50) NULL,
  [DocDate] [date] NULL,
  [WhsCode] [nvarchar](10) NULL,
  [Qty] [decimal](18, 0)...

Thank for the many replies....the idea is show the first row of others. The point is that I need to "call" same columns just to apply a filter into...

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 🙂