Can This Be Used With CTE

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

July 6th, 2015 9:27pm

Try

-- code #1 with
CTE as (
SELECT distinct [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] ),
CTE2 As ( SELECT distinct [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] )
SELECT T1.[Hotel Name], T1.Total Records], T1.[bad], T2.[Shipped], T2.[Awaiting Shipment] from CTE as T1 left outer join CTE2 as T2 on T1.[Hotel Name] = T2.[Hotel Name];

Free Windows Admin Tool Kit Click here and download it now
July 6th, 2015 9:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics