Solved

Difficult Left Join

Posted on 2010-11-23
6
184 Views
Last Modified: 2012-05-10
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
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34196515
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
 
LVL 18

Expert Comment

by:lludden
ID: 34196532

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
 
LVL 18

Expert Comment

by:deighton
ID: 34196541
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 34196551
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
 

Author Comment

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

Any problem with the points going to him?
0
 

Author Closing Comment

by:lrbrister
ID: 34201081
Worked perfectly...thanks
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question