troubleshooting Question

i need to union 2 cte if possible syntax

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft SQL ServerSQL
10 Comments1 Solution64 ViewsLast Modified:
i need to union 2 cte  if possible syntax

with cte as (select Comp_Code, Cust_ZZ_CD,
sum(mth00_AR) as sum_mth00_AR,
sum(mth00_Sales) as sum_mth00_Sales,
sum(mth01_Sales) as sum_mth01_Sales,
sum(mth02_Sales) as sum_mth02_Sales,
max(mth00_Days) as max_mth00_Days,
max(mth01_Days) as max_mth01_Days,
max(mth02_Days) as max_mth02_Days,
sum(mth03_AR) as sum_mth03_AR, -- 180
sum(mth03_Sales) as sum_mth03_Sales,  --180
sum(mth04_Sales) as sum_mth04_Sales, -- 180
sum(mth05_Sales) as sum_mth05_Sales,  -- 180
max(mth03_Days) as max_mth03_Days, -- 180
max(mth04_Days) as max_mth04_Days, -- 180
max(mth05_Days) as max_mth05_Days  --180

from [program_work].[dbo].[ARDataForDSOReporting]
group by 
Comp_Code,
Cust_ZZ_CD)

SELECT a.[Comp_Code]
      ,[Debitor]
      ,a.[Cust_ZZ_CD]
      ,[Cust_ZZ_SCD]
      ,[Cust_Credit_Account]
      ,[Cust_Coll_Segment]
      ,[Cust_Coll_Grp]
      ,[Cust_Coll_Specialist]
      ,[Cust_Name]
      ,[Track_Name]
      ,[SubTrack_Name]
      ,[CrdAcct_Name]
      ,[Cust_Coll_Spl_Name]
      ,[Bal_As_Of]
      ,[mth00_sales]
      ,[mth00_days]
      ,[mth00_AR]
      ,[mth01_sales]
      ,[mth01_days]
      ,[mth01_AR]
      ,[mth02_sales]
      ,[mth02_days]
      ,[mth02_AR]
      ,[mth03_sales]
      ,[mth03_days]
      ,[mth03_AR]
      ,[mth04_sales]
      ,[mth04_days]
      ,[mth04_AR]
      ,[mth05_sales]
      ,[mth05_days]
      ,[mth05_AR]
  FROM [program_work].[dbo].[ARDataForDSOReporting] a
  left outer join cte b on b.Comp_Code = a.Comp_Code and b.Cust_ZZ_CD = a.Cust_ZZ_CD
  
with cte1 as (select Comp_Code, Cust_ZZ_SCD,
sum(mth00_AR) as sum_mth00_AR,
sum(mth00_Sales) as sum_mth00_Sales,
sum(mth01_Sales) as sum_mth01_Sales,
sum(mth02_Sales) as sum_mth02_Sales,
max(mth00_Days) as max_mth00_Days,
max(mth01_Days) as max_mth01_Days,
max(mth02_Days) as max_mth02_Days,
sum(mth03_AR) as sum_mth03_AR, -- 180
sum(mth03_Sales) as sum_mth03_Sales,  --180
sum(mth04_Sales) as sum_mth04_Sales, -- 180
sum(mth05_Sales) as sum_mth05_Sales,  -- 180
max(mth03_Days) as max_mth03_Days, -- 180
max(mth04_Days) as max_mth04_Days, -- 180
max(mth05_Days) as max_mth05_Days  --180

from [program_work].[dbo].[ARDataForDSOReporting]
group by 
Comp_Code,
Cust_ZZ_SCD)

SELECT a1.[Comp_Code]
      ,[Debitor]
      ,a1.[Cust_ZZ_CD]
      ,[Cust_ZZ_SCD]
      ,[Cust_Credit_Account]
      ,[Cust_Coll_Segment]
      ,[Cust_Coll_Grp]
      ,[Cust_Coll_Specialist]
      ,[Cust_Name]
      ,[Track_Name]
      ,[SubTrack_Name]
      ,[CrdAcct_Name]
      ,[Cust_Coll_Spl_Name]
      ,[Bal_As_Of]
      ,[mth00_sales]
      ,[mth00_days]
      ,[mth00_AR]
      ,[mth01_sales]
      ,[mth01_days]
      ,[mth01_AR]
      ,[mth02_sales]
      ,[mth02_days]
      ,[mth02_AR]
      ,[mth03_sales]
      ,[mth03_days]
      ,[mth03_AR]
      ,[mth04_sales]
      ,[mth04_days]
      ,[mth04_AR]
      ,[mth05_sales]
      ,[mth05_days]
      ,[mth05_AR]
  FROM [program_work].[dbo].[ARDataForDSOReporting] a1
  left outer join cte1 b1 on b1.Comp_Code = a1.Comp_Code and b1.Cust_ZZ_SCD = a1.Cust_ZZ_SCD


-- This is not working
select * from cte
union
select * from
cte1



tHANKS
Fordraiders
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros