Hi,
I am currently working with a client that has a database structure that is
causing headaches in trying to create queries. Because the system is in
place and functioning and can't change the architecture, so i was hoping for
some assistance.
Their structure has Top Level Global databases that house data for each of
the companies such as Master Company Information, Invoicing, Tax etc.
On the secondary level is a Company Database which houses company specific
data...one database per company (and they have 5000+ companies)
My problem is that I often need to create queries that first query the
global companies table to retrieve a resultset based on conditions in which
then I have to query the company database for additional information. I am
looking for a way to do this perhaps in a single join statement if possible.
Example:
-- DOES NOT WORK
SELECT GC.CompanyID,(SELECT CT.TaxID,CT.CompanyAmount FROM
GC.CompanyID.dbo.CompanyTaxes CT WITH(NOLOCK) WHERE CT.Status='Paid') As
CompAmt
FROM Global.dbo.Companies GC WITH(NOLOCK)
WHERE GC.Status='active' and DATEPART(MONTH,GC.LastClose)>1
Is there anyway to do this and have it actually work? Note that the
CompanyID from the Global.dbo.Companies table is the same vale as the name
of the Company Database.
Thanks for any insight you might be able to provide,
Ron