You could successively cross apply

declare
  @price        int=100;

;with some_discounts_cte(first_pct, second_pct, third_pct) as (
    select 20.0, 20.0, 50.0)
select
...

drop table if exists #rev;
go
create table #rev(
  RevisionId int,
  AssemblySiteId int,
  Status nvarchar(200));
go

insert into #rev(RevisionId,AssemblySiteId)
values
(1900,200),
(2000,300),
(5000,800);

drop table if exists #location;
go
create table #location(
  locRevisionId int,
...

Maybe the short answer is to uncomment out the line:

--SR.DBLSTOCKONHAND as SOH

and uncomment out the FROM clause and change it to:

--left join STORERNG as...

from DAILYSALES DS
left join DISCHDR as DH on DS.INTOFFERCODE=DH.CTRCODE 
                 ...

drop table if exists dbo.#TestDates;
go
create table dbo.#TestDates(
  yr        int not null,
  mo      ...

An alternative to creating a TargetDate column would be to create a Priorities table which contains columns related to the priority.  Then calculate the TargetDate when you need it.  Priorities...

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