;with
details_summary_cte(Manage_GUID, details_row_count) as (
    select Manage_GUID,
           count(*) /* ... other aggregate functions */
 ...

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