SQL Union all plus variable

[infock12]

  Hi all,

I am using the below script but it gives me an error message Syntax error, misplaced/missing FROM: Concatenate(ActivityvPlan). Please could you let me know if there is anything wrong with the below? Many thanks.

LET @ExtractDate  = Text(Chr(39)&'2017/09/15'&Chr(39));


LET @ActualWeekEndDate = Chr(39) &Text(Date(WeekEnd(Today()),'YYYY/MM/DD')) &Chr(39);

//--Actual and planned activity

ActivityvPlan:
Load *,
Date([Week Ending],'DD/MM/YYYY') as WeekEnding,
'Past' as Source;
SQL
SELECT
AP.[FinYear],
AP.[Month_Name],
AP.[Week Ending],
AP.[Site],
AP.[Activity Type] AS 'Activity Type (Original)',
CASE
WHEN AP.[Activity Type] = 'OP' THEN 'Outpatient'
WHEN AP.[Activity Type] = 'DayCase' THEN 'Day Case'
ELSE AP.[Activity Type]
END AS 'Activity Type',
UPPER(AP.[Specialty]) as [Specialty],
AP.[NC],
AP.[Plan],
CASE WHEN AP.[Week Ending] > $(@ActualWeekEndDate) THEN 0 ELSE AP.[Actual] END AS 'Actual',
AP.[Actual] as 'RawActuals',
NULL as Booked,
GETDATE() AS 'Run Date',
'Actual and Planned' AS 'Category'
--ISNULL(MSI.[Site],'Unknown') AS 'Site for Reporting'
FROM [BH_INFORMATION].[DBO].[PLAN_AND_ACTUAL] AS AP
LEFT JOIN [BH_INFORMATION].[dbo].[DivisionLookup] DL ON AP.Specialty = DL.SPECIALTY
//--Link to site look-up table
LEFT OUTER JOIN [BH_PERFORMANCE].[dbo].[LKP_Site] AS LS
ON AP.[Site] = LS.[Raw Value]
AND LS.[Is Latest] = 'Y'
WHERE
AP.FinYear >= '2017/18'
AND AP.[Activity Type] <> 'Other';

CONCATENATE (ActivityvPlan)

//--Future activity
LET @ExtractDate = Text(Chr(39)&'2017/09/15'&Chr(39));


LET @ActualWeekEndDate = Chr(39) &Text(Date(WeekEnd(Today()),'YYYY/MM/DD')) &Chr(39);


LOAD *,

Date([Week Ending],'DD/MM/YYYY') as WeekEnding,
'Past' as Source;

SQL
SELECT
F.[FinYear],
F.[Month_Name],
F.[Week Ending],
F.[Site],
F.[Activity Type] AS 'Activity Type (Original)',
CASE
WHEN F.[Activity Type] = 'Daycases' THEN 'Day Case'
WHEN F.[Activity Type] = 'Elective Inpatients' THEN 'Elective'
WHEN F.[Activity Type] IN ('First Outpatient Attendances','Outpatient procedures','Subsequent outpatient Attendances') THEN 'Outpatient'
ELSE 'Other' END AS 'Activity Type',
F.[Specialty],
DL.[DIVISION] AS Division,
NULL AS 'NC',
NULL AS 'Plan', --Note: definitely do not use planned data in this future table as the planned fields are not correct in it
F.[Booked Activity] AS 'Actual',
//--(F.Rundate) AS 'Run Date',
'Future Booked' AS 'Category',
ISNULL(MSI.[Site],'Unknown') AS 'Site for Reporting'

FROM
[BH_INFORMATION].[DBO].[DASHBOARD_FUTURE] AS F
LEFT JOIN [BH_INFORMATION].[dbo].[DivisionLookup] DL ON F.Specialty = DL.SPECIALTY

//--Link to site look-up table
LEFT OUTER JOIN [BH_PERFORMANCE].[dbo].[LKP_Site] AS LS
ON F.[Site] = LS.[Raw Value]
AND LS.[Is Latest] = 'Y'

//--Link to master site table
LEFT OUTER JOIN
[BH_PERFORMANCE].[dbo].[Master_Site] AS MSI
ON LS.[Mapped Value] = MSI.[Site Original]
AND MSI.[Is Latest] = 'Y'

WHERE
F.FinYear >= '2017/18'
AND CAST(F.Rundate AS DATE) = @ExtractDate
AND (CASE
WHEN F.[Activity Type] = 'Daycases' THEN 'Day Case'
WHEN F.[Activity Type] = 'Elective Inpatients' THEN 'Elective'
WHEN F.[Activity Type] IN ('First Outpatient Attendances','Outpatient procedures','Subsequent outpatient Attendances') THEN 'Outpatient'
ELSE 'Other' END) <> 'Other';  

[Peter_Cammaert]

CONCATENATE is a prefix for a LOAD statement. If we keep that in mind, then why are there two complete LET statements between the CONCATENATE (ActivityvPlan) prefix and the next LOAD?

Place the CONCATENATE(ActivityvPlan) line two statements lower, and all will be fine (with the statement syntax, that is)

[infock12]

Thanks for the quick response Peter. I am assuming something like below? It is still giving me the error message 'ErrorSource: Microsoft SQL Server Native Client 11.0, ErrorMsg: Incorrect syntax near ')'. Not sure how can I identify which row this error is. I looked line by line but I can't find anything obvious.

LET @ExtractDate  = Text(Chr(39)&'2017/09/15'&Chr(39));


LET @ActualWeekEndDate = Chr(39) &Text(Date(WeekEnd(Today()),'YYYY/MM/DD')) &Chr(39);

//--Actual and planned activity

ActivityvPlan:
Load *,
Date([Week Ending],'DD/MM/YYYY') as WeekEnding,
'Past' as Source;
SQL
SELECT
AP.[FinYear],
AP.[Month_Name],
AP.[Week Ending],
AP.[Site],
AP.[Activity Type] AS 'Activity Type (Original)',
CASE
WHEN AP.[Activity Type] = 'OP' THEN 'Outpatient'
WHEN AP.[Activity Type] = 'DayCase' THEN 'Day Case'
ELSE AP.[Activity Type]
END AS 'Activity Type',
UPPER(AP.[Specialty]) as [Specialty],
AP.[NC],
AP.[Plan],
CASE WHEN AP.[Week Ending] > $(@ActualWeekEndDate) THEN 0 ELSE AP.[Actual] END AS 'Actual',
AP.[Actual] as 'RawActuals',
NULL as Booked,
GETDATE() AS 'Run Date',
'Actual and Planned' AS 'Category'
--ISNULL(MSI.[Site],'Unknown') AS 'Site for Reporting'
FROM [BH_INFORMATION].[DBO].[PLAN_AND_ACTUAL] AS AP
LEFT JOIN [BH_INFORMATION].[dbo].[DivisionLookup] DL ON AP.Specialty = DL.SPECIALTY
//--Link to site look-up table
LEFT OUTER JOIN [BH_PERFORMANCE].[dbo].[LKP_Site] AS LS
ON AP.[Site] = LS.[Raw Value]
AND LS.[Is Latest] = 'Y'
WHERE
AP.FinYear >= '2017/18'
AND AP.[Activity Type] <> 'Other';



//--Future activity
LET @ExtractDate  = Text(Chr(39)&'2017/09/15'&Chr(39));
//
//
LET @ActualWeekEndDate = Chr(39) &Text(Date(WeekEnd(Today()),'YYYY/MM/DD')) &Chr(39);

CONCATENATE (ActivityvPlan)

FutureActivity:
LOAD *,

Date([Week Ending],'DD/MM/YYYY') as WeekEnding,
'Past' as Source;

SQL
SELECT
F.[FinYear],
F.[Month_Name],
F.[Week Ending],
F.[Site],
F.[Activity Type] AS 'Activity Type (Original)',
CASE
WHEN F.[Activity Type] = 'Daycases' THEN 'Day Case'
WHEN F.[Activity Type] = 'Elective Inpatients' THEN 'Elective'
WHEN F.[Activity Type] IN ('First Outpatient Attendances','Outpatient procedures','Subsequent outpatient Attendances') THEN 'Outpatient'
ELSE 'Other' END AS 'Activity Type',
F.[Specialty],
DL.[DIVISION] AS Division,
NULL AS 'NC',
NULL AS 'Plan', //--Note: definitely do not use planned data in this future table as the planned fields are not correct in it
F.[Booked Activity] AS 'Actual',
F.Rundate) AS 'Run Date',
'Future Booked' AS 'Category',
ISNULL(MSI.[Site],'Unknown') AS 'Site for Reporting'

FROM
[BH_INFORMATION].[DBO].[DASHBOARD_FUTURE] AS F
LEFT JOIN [BH_INFORMATION].[dbo].[DivisionLookup] DL ON F.Specialty = DL.SPECIALTY

//--Link to site look-up table
LEFT OUTER JOIN [BH_PERFORMANCE].[dbo].[LKP_Site] AS LS
ON F.[Site] = LS.[Raw Value]
AND LS.[Is Latest] = 'Y'

//--Link to master site table
LEFT OUTER JOIN
[BH_PERFORMANCE].[dbo].[Master_Site] AS MSI
ON LS.[Mapped Value] = MSI.[Site Original]
AND MSI.[Is Latest] = 'Y'

WHERE
F.FinYear >= '2017/18'
AND CAST(F.Rundate AS DATE) = @ExtractDate
AND (CASE
WHEN F.[Activity Type] = 'Daycases' THEN 'Day Case'
WHEN F.[Activity Type] = 'Elective Inpatients' THEN 'Elective'
WHEN F.[Activity Type] IN ('First Outpatient Attendances','Outpatient procedures','Subsequent outpatient Attendances') THEN 'Outpatient'
ELSE 'Other' END) <> 'Other';

[infock12]

Hi Peter,

I have fixed the above error but it is throwing out another error message: ErrorSource: Microsoft SQL Server Native Client 11.0, ErrorMsg: Must declare the scalar variable "@ExtractDate".

Any help is appreciated!

[Peter_Cammaert · Accepted solution]

In the second SQL statement, you use something like:

AND CAST(F.Rundate AS DATE) = @ExtractDate

Replace @ExtractDate with $(@ExtractDate) and try again.

[infock12]

Excellent, that worked like a charm Peter! Thank you very much and have a good weekend!