?
Solved

Very complex cross database select statement

Posted on 2010-11-30
2
Medium Priority
?
217 Views
Last Modified: 2012-05-10
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

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
2 Comments
 
LVL 7

Accepted Solution

by:
Cboudroz earned 2000 total points
ID: 34240128
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
 

Author Closing Comment

by:lrbrister
ID: 34240229
Great!
Anything you ever need...mayhem, assault, robbery...just let me know
:-D
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 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