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

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

Open in new window

Thanks
fordraiders

Microsoft SQL ServerSQL

Last Comment

PortletPaul

8/22/2022 - Mon

Bill Prew

3/1/2019

Hard to know without knowing your data, but perhaps something like this:

SELECT DISTINCT dso180.Debitor
      ,dso180.Comp_Code
      ,dso180.Cust_ZZNA_CD
      ,dso180.Cust_ZZNA_SCD
      ,dso180.DSO AS dso_180
      ,dso270.DSO AS dso_270
      ,dso360.DSO AS dso_360
      ,dso450.DSO AS dso_450
FROM program_work.dbo.VW_VIEW_DSO_180 dso180
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_270 dso270 ON dso270.Debitor = dso180.Debitor
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_360 dso360 ON dso360.Debitor = dso180.Debitor
LEFT OUTER JOIN program_work.dbo.VW_VIEW_DSO_450 dso450 ON dso450.Debitor = dso180.Debitor
WHERE dso180.Cust_ZZNA_CD IS NOT NULL

Open in new window

»bp

ASKER CERTIFIED SOLUTION

Scott Pletcher

3/1/2019

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Fordraiders

3/3/2019

ASKER

Thanks folks as always !!

PortletPaul

3/8/2019

I suspect the proposed left joins require use of the row_number() output:

LEFT OUTER JOIN cte2 ON cte1.Debitor = cte2.Debitor AND cte2.row_num = 1
LEFT OUTER JOIN cte3 ON cte1.Debitor = cte3.Debitor AND cte3.row_num = 1
LEFT OUTER JOIN cte4 ON cte1.Debitor = cte4.Debitor AND cte4.row_num = 1

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes