Avatar of Fordraiders
Fordraiders
Flag for United States of America

asked on 

i need to union 2 cte if possible syntax

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

Open in new window




tHANKS
Fordraiders
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon