I am trying to run these two queries together, stand-alone they produce the results I need, but I am wanting to (if possible) run them together and get 1 result set returned. I thought possibly a CTE was the way to go, but I get multiple errors when I run this (all syntax), can someone show me what to do to fix this please?
With CTE AS ( Select DISTINCT([Hotel Name]) As [Hotel Name] ,COUNT(ID) As [Total] ,SUM(case when dl.[bad] IS NOT NULL then 1 else 0 end) As [bad] FROM [brimston].[solda].[dbo].[donloadable] dl GROUP BY [Hotel Name] ORDER BY [Hotel Name] ASC ), CTE2 As ( Select Distinct(Hotel Name) As [Hotel Name] ,SUM(case when order_status IN ('Processed', 'Shipped', 'ready to ship') then 1 else 0 end) As [Shipped] ,SUM(case when order_status IN ('Pending', 'On Hold') and LeadFromNurturing IS NULL then 1 else 0 end) As [Awaiting Shipment] FROM [dbo].[onholdinfo] d where [Hotel Name] IN (Select [Hotel Name] from [brimston].[solda].[dbo].[donloadable]) Group By [Hotel Name] Order By [Hotel Name] ASC ) Select CTE.[Hotel Name] ,CTE.Total Records], ,CTE.[bad] FROM CTE UNION ALL SELECT CTE2.[Shipped] ,CTE2.[Awaiting Shipment] FROM CTE2