SQL Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Question

Author

Topic

duffy202
Starting Member

12 Posts

Posted - 2011-08-03 : 05:07:11

Hi guys,

i'm looking for a bit of guidance with an SQL query.

Here is what i am trying to do.

I have 2 seperate databases.
1 database has company details and calls logged for that company.
The 2nd database holds just 2 values: companyname & status
(E.G. Microsoft Corporation - Successful)

These databases aren't related at all and have no communication with each other at the moment. I'm trying to merge data from both databases to display a report with the company name, status and calls logged for that company.

I would like to know how to run a query that will
match up the company name in database 1 to the company name in database 2.

the finished result I am looking for is to have a list of company names - the status of the company and a list of any calls logged against that company.

Any help would be appreciated.

Thanks,
M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 05:10:54

like this with limited info you provided?


SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t2.companyname = t1.companyname

I'm not sure whether you're refering to tables themselves or tables in dbs which are on different server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-03 : 05:45:19

Thanks for the reply.

Here is some more information.
I am looking to extract data from 2 databases called CRM & TECH.

from the TECH database I am looking to extract 2 peices of information 'company_name' & 'call_status'
E.G. Microsoft Corporation - Failed

from the CRM database i am looking to get 'company_name' & a list of all calls logged under that company (If any).
E.G. Microsoft Corporation - CALLREF12345

the 'company_name' field will be the unique link between the 2 databases.

so the end result will be something like

Microsoft Corporation - Failed - CALLREF12345

at the moment the databases are located on the same server and they should be able to communicate without any issues.
the databases are completely seperate and have nothing to do with each-other but they do hold information on the same companies (the company name being unique)

Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 05:48:30

which are tables where information is stored in both dbs?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-03 : 06:15:33

from the CRM database:
Account table - holds customer information inc. company_name
Incident table - holds case information for customers

from the TECH database:
Company table - holds company name, that's it.
Status table - holds status.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 07:37:36

do something like


USE [TECH]

SELECT columns
FROM CRM.dbo.Account a 
JOIN CRM.dbo.Incident i
ON i.customer = a.customer
JOIN Company c
ON a.company_name = c.company_name
JOIN Status s
ON s.RelatedField = {a/i}.RelatedField

Make sure you put correct column names which I dont have visibility of from the tables (blue code above)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

duffy202
Starting Member

12 Posts

Posted - 2011-08-03 : 17:58:50

Thanks for your help, I managed to get it working :D

I have another question. Do you know how to create a start & end date parameter?

I would like it to ask the user for the start and end date (preferably allow them to pick from a calendar). if this isn't possible do you know how to filter the results where CallCreatedDate=Today's date -1 Month?

Thanks again for your help, Much appreciated

StefanK
Starting Member

4 Posts

Posted - 2011-08-04 : 02:34:18

DECLARE @start DATE
DECLARE @end DATE

YOURQUERYHERE
WHERE
i.CallCreatedDate BETWEEN @start AND @end

or without parameters
WHERE
i.CallCreatedDate BETWEEN DATEADD(MONTH,-1,GETDATE()) AND GETDATE()

duffy202
Starting Member

12 Posts

Posted - 2011-08-04 : 02:53:36

Works like a charm, cheers ace!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources