Very complex cross database select statement

In my select statement below I need the ability to change the database I'm selecting from on a record by record basis.

So...when Left(p.afsSource,2) = 'LT'
I pull from proc_lt.dbo.P_PaymentStreams

Else
I pull from  crmprod_01.dbo.P_PaymentStreams


The top part will ALWAYS pull from proc_cfa.dbo.P_AvailableForSale
Declare @OrdTranID int  
Set @OrdTranID = 9001
Select  Left(p.afsSource,2) as busType,    
        SubString(p.afsSource,4,4) as DealID,  
		Sum(p.afsAMount) as afsAmount,
		st.cp as CompoundingMethod         
from    proc_cfa.dbo.P_AvailableForSale p   
        LEFT JOIN (  
                Select              
                        ps.DealID    
                        ,CASE WHEN COUNT(ps.PmtFrequency) > 1 THEN 'Split' ELSE MIN(ps.PmtFrequency) END cp    
                        ,MAX(ps.loanDate) LoanDate  
                from    proc_lt.dbo.P_PaymentStreams ps  
                Group by ps.DealID   
        ) st ON st.DealId = SubString(p.afsSource,4,4)  
where   p.afsTransAction = @OrdTranID    
group by Left(p.afsSource,2), SubString(p.afsSource,4,4), st.cp

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
CboudrozCommented:
there you go
Declare @OrdTranID int  
Set @OrdTranID = 9001
Select  Left(p.afsSource,2) as busType,    
        SubString(p.afsSource,4,4) as DealID,  
		Sum(p.afsAMount) as afsAmount,
		ISNULL(st.cp,st2.cp) as CompoundingMethod         
from    proc_cfa.dbo.P_AvailableForSale p   

        LEFT JOIN (  
                Select              
                        ps.DealID    
                        ,CASE WHEN COUNT(ps.PmtFrequency) > 1 THEN 'Split' ELSE MIN(ps.PmtFrequency) END cp    
                        ,MAX(ps.loanDate) LoanDate  
                from    proc_lt.dbo.P_PaymentStreams ps  
                Group by ps.DealID   
        ) st ON st.DealId = SubString(p.afsSource,4,4) 
				AND Left(p.afsSource,2) = 'LT'

        LEFT JOIN (  
                Select              
                        ps2.DealID    
                        ,CASE WHEN COUNT(ps2.PmtFrequency) > 1 THEN 'Split' ELSE MIN(ps2.PmtFrequency) END cp    
                        ,MAX(ps2.loanDate) LoanDate  
                from    crmprod_01.dbo.P_PaymentStreams ps2
                Group by ps2.DealID   
        ) st2 ON st2.DealId = SubString(p.afsSource,4,4) 
				AND Left(p.afsSource,2) <> 'LT'
 
where   p.afsTransAction = @OrdTranID    
group by Left(p.afsSource,2), SubString(p.afsSource,4,4), isnull(st.cp, st2.cp)

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Great!
Anything you ever need...mayhem, assault, robbery...just let me know
:-D
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.