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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Scott PletcherSenior DBACommented:
If you want only a single row per Debitor, then something more like this:


;with cte1 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO],row_num) as
(SELECT [Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO], 
        ROW_NUMBER() OVER(PARTITION BY Debitor ORDER BY Cust_ZZNA_CD DESC) AS row_num
      FROM [program_work].[dbo].[VW_VIEW_DSO_180] dso180
      where dso180.[Cust_ZZNA_CD] is not null)
      ,

 cte2 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO],row_num) as
(SELECT [Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO], 
        ROW_NUMBER() OVER(PARTITION BY Debitor ORDER BY Cust_ZZNA_CD DESC) AS row_num
      FROM [program_work].[dbo].[VW_VIEW_DSO_270] dso270)
      ,

 cte3 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO],row_num) as
(SELECT [Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO], 
        ROW_NUMBER() OVER(PARTITION BY Debitor ORDER BY Cust_ZZNA_CD DESC) AS row_num
      FROM [program_work.[dbo].[VW_VIEW_DSO_360] dso360)
      ,
    
 cte4 ([Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO],row_num) as
(SELECT [Debitor],[Comp_Code],[Cust_ZZNA_CD],[Cust_ZZNA_SCD],[DSO], 
        ROW_NUMBER() OVER(PARTITION BY Debitor ORDER BY Cust_ZZNA_CD DESC) AS row_num
      FROM [program_work.[dbo].[VW_VIEW_DSO_450] dso450)
      
SELECT  cte1.[Debitor]
       ,cte1.[Comp_Code]
      ,cte1.[Cust_ZZNA_CD]
      ,cte1.[Cust_ZZNA_SCD]
      ,cte1.[DSO] as dso_180
      ,cte2.dso as dso_270
      ,cte3.dso as dso_360
      ,cte4.DSO as dso_450
  FROM cte1 

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks folks as always !!
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.