Ok ok I changed the decode to 'South Africa' and now it does work.  Misdiagnosed due to misleading code?  Yea, if it scans once and there's no dependency then it...

Wait a sec.  North Africa is in the code twice!

The bottom grouping is the code with tally ordered by 1,2,3.  The top is UNION ALL with same ordering

When CROSS JOIN with the tally table the resulting ordering may not align with Region.  It's not known which region will be 1 or 2 or ... unless joined back...

When the code posted above runs it produces the following output:

CountryStatenamesurnameagegender
USAlabamaJohnDon45NULL
CanadaTorontoMarkBagwell35Male

This is exactly what was requested no?  Is it a requirement the query be dynamic because the...

drop table if exists dbo.TestAZURE_JSON;
go
CREATE TABLE dbo.TestAZURE_JSON (
RowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Country varchar(10),
[State] varchar(10),
[JSON_Value] nvarchar(max));
go

INSERT dbo.TestAZURE_JSON(Country, [State], [JSON_Value]) VALUES
('US', 'Alabama', '{"name":"John","surname":"Don","age":45}'),
('Canada', 'Toronto', '{"name":"Mark","surname":"Bagwell","age":35,"gender":"Male"}');

select 
...

• This reply was modified 5 years, 10 months ago by Steve Collins. Reason: made JSON nvarchar(max)

This uses the daterange function described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

(the actual daterange code used is closest to this one)

drop table if exists dbo.TestUserAccount;
go
create table dbo.TestUserAccount(
  AccountName varchar(20) primary...

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