Martin I found the issue
From Microsoft
https://docs.microsoft.com/en-us/sql...ql-server-2017

specifically
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
Now my original sample has the semicolon as it is required, If you test statement I originallyy posted in MSSQL server Management Studio you will get an error, the semi colon is required. to be a legitimate statement

Code:
declare @StartDate Date 
Set @StartDate = '2019-07-08'
declare @Centre Char(30)
Set @Centre = (select company_name from sysinfo)
;   <---------------------------------------------------------------
with ChildBooking as (
select 
childCode,
parentCode,
etc
The sample you sent me was a simpler CTE without the need to have the semicolon as there were no prior statement to the cte declaration

This is a show stopper as far as I am concerned as I use CTE for most of my RDS reports.

Also another concern maybe multiple CTEs , they need to be seperated by a comma, I will need to see if this is an issue also