asked on

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

Guy Hengel [angelIII / a3]

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

lludden

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

deighton

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

ASKER CERTIFIED SOLUTION

Bhavesh Shah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

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

Any problem with the points going to him?

Larry Brister

ASKER

Worked perfectly...thanks