Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Bill Prew

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
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

ASKER
Thanks folks as always !!
PortletPaul

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