[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Difficult Left Join

MY attached code works...I create the #temp table...do my join...and the output is what we want to see.

However...is there a way to NOT create a temp table and do the Left Join with my #temp select as a table in the left join section?

Instead of this...join to my select instead of a temp table
....Left Join #temp z on Left(a.afsSource,7) = z.src
Begin
		select left(afssource,7) src,
				 (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld
		Into #temp
		from proc_cfa.dbo.P_AvailableForSale  
		group by left(afssource, 7)
		order by left(afssource,7)
	Select	a.afsid,
			a.afsSource,
			case when Left(a.afsSource,2) = 'LT' then 
					lt.status else 
					ss.status end as [status],
			a.afsDate,
			a.afsAmount,			
			a.afsSourceDetail,
			a.afsStatus,
			IsNull(a.afsTransaction,0) as afsTransaction,
--			100 * (1-a.afsAmount / sum(x.afsAmount)) as pctHeld,
			z.percentHeld as pctHeld
	From	proc_cfa.dbo.P_AvailableForSale a
					Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID
					Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID
					Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource
					Left Join #temp z on Left(a.afsSource,7) = z.src
	Where	a.afsTransaction = 0
			and
			(
				(a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)
					and
				(YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)
					and
				case when Left(a.afsSource,2) = 'LT' then 
					lt.status else 
					ss.status end = 
						IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then 
									lt.status else 
									ss.status end)
			)
	group by	a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount, 
				a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld
	Order By	a.afsSource,a.afsSourceDetail, a.afsid
End

Open in new window

0
lrbrister
Asked:
lrbrister
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use WITH construct:
Begin
;with temp as (
                select left(afssource,7) src,
                                 (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld
--                Into #temp
                from proc_cfa.dbo.P_AvailableForSale  
                group by left(afssource, 7)
                order by left(afssource,7)
)
        Select  a.afsid,
                        a.afsSource,
                        case when Left(a.afsSource,2) = 'LT' then 
                                        lt.status else 
                                        ss.status end as [status],
                        a.afsDate,
                        a.afsAmount,                    
                        a.afsSourceDetail,
                        a.afsStatus,
                        IsNull(a.afsTransaction,0) as afsTransaction,
--                      100 * (1-a.afsAmount / sum(x.afsAmount)) as pctHeld,
                        z.percentHeld as pctHeld
        From    proc_cfa.dbo.P_AvailableForSale a
                                        Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID
                                        Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID
                                        Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource
                                        Left Join temp z on Left(a.afsSource,7) = z.src
        Where   a.afsTransaction = 0
                        and
                        (
                                (a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)
                                        and
                                (YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)
                                        and
                                case when Left(a.afsSource,2) = 'LT' then 
                                        lt.status else 
                                        ss.status end = 
                                                IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then 
                                                                        lt.status else 
                                                                        ss.status end)
                        )
        group by        a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount, 
                                a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld
        Order By        a.afsSource,a.afsSourceDetail, a.afsid
End

Open in new window

0
 
lluddenCommented:

Begin
                WITH cteData (src, percentHeld)
                AS (
		select left(afssource,7) src,
				 (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld
		from proc_cfa.dbo.P_AvailableForSale  
		group by left(afssource, 7)
		order by left(afssource,7)
                )
	Select	a.afsid,
			a.afsSource,
			case when Left(a.afsSource,2) = 'LT' then 
					lt.status else 
					ss.status end as [status],
			a.afsDate,
			a.afsAmount,			
			a.afsSourceDetail,
			a.afsStatus,
			IsNull(a.afsTransaction,0) as afsTransaction,
--			100 * (1-a.afsAmount / sum(x.afsAmount)) as pctHeld,
			z.percentHeld as pctHeld
	From	proc_cfa.dbo.P_AvailableForSale a
					Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID
					Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID
					Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource
					Left Join cteData  z on Left(a.afsSource,7) = z.src
	Where	a.afsTransaction = 0
			and
			(
				(a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)
					and
				(YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)
					and
				case when Left(a.afsSource,2) = 'LT' then 
					lt.status else 
					ss.status end = 
						IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then 
									lt.status else 
									ss.status end)
			)
	group by	a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount, 
				a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld
	Order By	a.afsSource,a.afsSourceDetail, a.afsid
End

Open in new window

0
 
deightonCommented:
try using the 'WITH' keyword


WITH YourInitialTable AS
(
select left(afssource,7) src,
                         (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld
            Into #temp
            from proc_cfa.dbo.P_AvailableForSale  
            group by left(afssource, 7)
            order by left(afssource,7)
)
      Select      a.afsid,
                  a.afsSource,
                  case when Left(a.afsSource,2) = 'LT' then
                              lt.status else
                              ss.status end as [status],
                  a.afsDate,
                  a.afsAmount,                  
                  a.afsSourceDetail,
                  a.afsStatus,
                  IsNull(a.afsTransaction,0) as afsTransaction,
--                  100 * (1-a.afsAmount / sum(x.afsAmount)) as pctHeld,
                  z.percentHeld as pctHeld
      From      proc_cfa.dbo.P_AvailableForSale a
                              Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID
                              Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID
                              Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource
                              Left Join YourInitialTable  z on Left(a.afsSource,7) = z.src
      Where      a.afsTransaction = 0
                  and
                  (
                        (a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)
                              and
                        (YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)
                              and
                        case when Left(a.afsSource,2) = 'LT' then
                              lt.status else
                              ss.status end =
                                    IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then
                                                      lt.status else
                                                      ss.status end)
                  )
      group by      a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount,
                        a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld
      Order By      a.afsSource,a.afsSourceDetail, a.afsid
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Bhavesh ShahLead AnalysistCommented:
Hi,

Check out this...

Without #temp table
Select  a.afsid,
                        a.afsSource,
                        case when Left(a.afsSource,2) = 'LT' then 
                                        lt.status else 
                                        ss.status end as [status],
                        a.afsDate,
                        a.afsAmount,                    
                        a.afsSourceDetail,
                        a.afsStatus,
                        IsNull(a.afsTransaction,0) as afsTransaction,
--                      100 * (1-a.afsAmount / sum(x.afsAmount)) as pctHeld,
                        z.percentHeld as pctHeld
        From    proc_cfa.dbo.P_AvailableForSale a
                Left Join proc_lt.dbo.P_Deals lt ON SubString(a.afsSource,4,4) = lt.DealID
                Left Join crmprod_01.dbo.P_Deals ss ON SubString(a.afsSource,4,4) = ss.DealID
                Left Join proc_cfa.dbo.P_AvailableForSale x on a.afsSource = x.afsSource
                Left Join (select left(afssource,7) src,
                                 (1 -sum(case when (afsstatus = 1) then afsamount else 0 end)/sum(afsamount)) *100 as percentHeld
                            from proc_cfa.dbo.P_AvailableForSale  
							group by left(afssource, 7)
							)z on Left(a.afsSource,7) = z.src
        Where   a.afsTransaction = 0
                        and
                        (
                                (a.afsAMount > @minAmount - 1 and a.afsAMount < @maxAmount + 1)
                                        and
                                (YEAR(a.afsDate) > @startYear - 1 and YEAR(a.afsDate) < @endYear + 1)
                                        and
                                case when Left(a.afsSource,2) = 'LT' then 
                                        lt.status else 
                                        ss.status end = 
                                                IsNull(@dealStatus,case when Left(a.afsSource,2) = 'LT' then 
                                                                        lt.status else 
                                                                        ss.status end)
                        )
        group by        a.afsid, a.afsSource, a.afsSourceDetail, a.afsDate, a.afsAMount, 
                                a.afsTransaction, a.afsStatus,ss.status, lt.status, z.percentHeld
        Order By        a.afsSource,a.afsSourceDetail, a.afsid

Open in new window

0
 
lrbristerAuthor Commented:
Hey folks,
  I was able to drop Brichsoft: solution straight in with no modifications.

Any problem with the points going to him?
0
 
lrbristerAuthor Commented:
Worked perfectly...thanks
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now