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

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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

Fordraiders

Author

Commented:
Thanks folks as always !!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial