asked on
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