Complex Left Join

I need to do a left join between my two attached select statements on

First Select                             Second Select
SubString(afsSource,4,4)  =  DealID
Select  Left(afsSource,2) as busType,  
        SubString(afsSource,4,4) as DealID,
		Sum(afsAMount) as afsAmount       
from    proc_cfa.dbo.P_AvailableForSale  
where   afsTransAction = @OrdTranID  
group by Left(afsSource,2), SubString(afsSource,4,4)


Select            
        DealID  
        ,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  
        ,MAX(loanDate) LoanDate
from    proc_lt.dbo.P_PaymentStreams 
Group by DealID 
order by DealID

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Erick37Connect With a Mentor Commented:
You could use a CTE (connon table expression) to make it easier:
;WITH cte_PaymentStreams (DealID,cp,LoanDate) AS
(
	Select            
			DealID  
			,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  
			,MAX(loanDate) LoanDate
	from    proc_lt.dbo.P_PaymentStreams 
	Group by DealID 
	order by DealID
)
Select  Left(afsSource,2) as busType,  
        SubString(afsSource,4,4) as DealID,
		Sum(afsAMount) as afsAmount       
from    proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN cte_PaymentStreams B ON SubString(A.afsSource,4,4)  =  B.DealID 
where   afsTransAction = @OrdTranID  
group by Left(afsSource,2), SubString(afsSource,4,4)

Open in new window

0
 
patternedCommented:
0
 
Erick37Commented:
Correction: CTE (common table expression)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
deightonCommented:
don't do a left join, join on all rows, then select time matches

select ...From A,B Where YourTimeDifference(A.Time, B.Time) < 10

then you could use the UNION statement to add rows from first table that are not matched

or you could make the first select into a table variable, then select all rows from it Unioned with rows not counted in it




0
 
Larry Bristersr. DeveloperAuthor Commented:
Erick37:
How do I get the cp column from the top select?
0
 
gamarrojgqConnect With a Mentor Commented:
Try this, with this approach you can add the columns from the inner query to the main query but with an aggregate function since you main query have a Group clause
Select  Left(P_AvailableForSale.afsSource,2) as busType,  
        SubString(P_AvailableForSale.afsSource,4,4) as DealID,
		Sum(P_AvailableForSale.afsAMount) as afsAmount       
from    proc_cfa.dbo.P_AvailableForSale  
	LEFT JOIN (
		Select            
		        P_PaymentStreams.DealID  
		        ,CASE WHEN COUNT(P_PaymentStreams.PmtFrequency) > 1 THEN 'Split' ELSE MIN(P_PaymentStreams.PmtFrequency) END cp  
		        ,MAX(P_PaymentStreams.loanDate) LoanDate
		from    proc_lt.dbo.P_PaymentStreams 
		Group by P_PaymentStreams.DealID 
	) SecondTable ON SecondTable.DealId = SubString(P_AvailableForSale.afsSource,4,4)
where   P_AvailableForSale.afsTransAction = @OrdTranID  
group by Left(P_AvailableForSale.afsSource,2), SubString(P_AvailableForSale.afsSource,4,4)

Open in new window

0
 
Erick37Commented:
Like this:
;WITH cte_PaymentStreams (DealID,cp,LoanDate) AS
(
	Select            
			DealID  
			,CASE WHEN COUNT(PmtFrequency) > 1 THEN 'Split' ELSE MIN(PmtFrequency) END cp  
			,MAX(loanDate) LoanDate
	from    proc_lt.dbo.P_PaymentStreams 
	Group by DealID 
	order by DealID
)
Select  Left(afsSource,2) as busType,  
        SubString(afsSource,4,4) as DealID,
	Sum(afsAMount) as afsAmount,   
        B.cp,
        B.LoanDate,
        B.DealID    
from    proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN cte_PaymentStreams B ON SubString(A.afsSource,4,4)  =  B.DealID 
where   afsTransAction = @OrdTranID  
group by Left(afsSource,2), SubString(afsSource,4,4)

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Erick37: and gamarrojgq:

Both answers worked fine...and valid applications.

I would like to award points to Erick37: with a nod to gamarrojgq with some points as well.

Anu problems with a 350/150 split?

I am also going to post an additional question based on this solution
0
 
gamarrojgqCommented:
Irbrister,

It sounds fine to me :)
0
 
Larry Bristersr. DeveloperAuthor Commented:
Excellent guys.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.