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, 2 months ago by Steve Collins. Reason: Added within group order by

If tvf's are allowed then I'd borrow some of Phil's code

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

Nah... it's easier than that.  Create a stored procedure that does the work, give the spreadsheet user privs to execute it, and have them execute the stored procedure from...

What Frederico wrote at the ende: "note that if this query is called multiple times for location then it should be changed so that all locations are supplied (alongside their...

Phil's solution works well.  It's more flexible in that there could be more than 1 character to prefix the []'s

Ok here's a tvf which maybe makes it more maintainable and readable.  Fwiw I don't follow Brian's approach and keeping data manipulation logic in the database seems OK imo.  To...