troubleshooting Question

using 4 CTE's and trying to run query  is there a faster way

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft SQL ServerSQL
4 Comments1 Solution79 ViewsLast Modified:
using 4 CTE and trying to run query..
is there a better and faster way  ?

Need distinct [Debitor]



with cte1 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work].[dbo].[VW_VIEW_DSO_180] dso180)
      ,
 cte2 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work].[dbo].[VW_VIEW_DSO_270] dso270)
      ,
 cte3 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work.[dbo].[VW_VIEW_DSO_360] dso360)
      ,
      
 cte4 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO]) as
(SELECT distinct [Debitor]
       ,[Comp_Code]
      ,[Cust_ZZNA_CD]
      ,[Cust_ZZNA_SCD]
      ,[DSO] 
      FROM [program_work.[dbo].[VW_VIEW_DSO_450] dso450)
      
SELECT distinct dso180.[Debitor]
       ,dso180.[Comp_Code]
      ,dso180.[Cust_ZZNA_CD]
      ,dso180.[Cust_ZZNA_SCD]
      ,dso180.[DSO] as dso_180
      ,cte2.dso as dso_270
      ,cte3.dso as dso_360
      ,cte4.DSO as dso_450
  FROM [program_work].[dbo].[VW_VIEW_DSO_180] dso180

left outer join
  cte2
  on
 dso180.Debitor = cte2.Debitor
  
left outer join
 cte3
  on
  dso180.Debitor = cte3.Debitor
  
  left outer join
  cte4
  on
  dso180.Debitor = cte4.Debitor

 where dso180.[Cust_ZZNA_CD] is not null



Thanks
fordraiders
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 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 4 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