help regarding geting info from different database

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

SQL Server 2005 Forums

Transact-SQL (2005)

help regarding geting info from different database

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-04-02 : 17:13:35

I have 5 differnet databases and all of them have the same table name and same fields with different info init. I am trying to combine all the info from the database into a report. I have sql to get the infomation from the database. Same sql can be used to get the info from the other databases.
I want to report look like this
TC stands for TotalCalls
The value under Tcdatabase1 2 or 3 will be the number of calls the agent got for the database under the disposition hangup.

Name Disposition TCDatabase1 TCDatabase2 TCdatabase3 Total
joni hangup 2 0 3 5
roni1 hangup 1 2 2 5

Here is the SQL for all the database which can be used to get the info from differnet database.

SELECT LocalUserId as [name],disposition,
COUNT(*) AS TCDatabase1
FROM
testdatabase.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
ORDER BY LocalUserID, Disposition

here is the table struct
CREATE TABLE [dbo].[tblCallReceived](
[CallReceivedID] [int] IDENTITY(1,1) NOT NULL,
[CallID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallIDKey] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParseData] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RemoteTN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StationName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LineName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedWorkgroup] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallDate] [datetime] NOT NULL CONSTRAINT [DF_tblCallReceived_CallDate] DEFAULT (getdate()),
[DNIS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalUserID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalTN] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RemoteName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallState] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CallStateString] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Disposition] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

theboyholty
Posting Yak Master

226 Posts

Posted - 2009-04-03 : 06:33:38

I'm not going to write your whole query for you but if it was me i'd unionise the five queries then add in an identifier.
For example

SELECT CallReceivedID,CallID,CallIDKey,'DBASE1' as DBase
FROM testdatabase.dbo.tblCallReceived
WHERE CallDate BETWEEN '3/26/2009' AND '3/28/2009'

UNION

SELECT CallReceivedID,CallID,CallIDKey,'DBASE2' as DBase
FROM testdatabase2.dbo.tblCallReceived
WHERE CallDate BETWEEN '3/26/2009' AND '3/28/2009'

UNION

SELECT CallReceivedID,CallID,CallIDKey,'DBASE3' as DBase
FROM testdatabase3.dbo.tblCallReceived
WHERE CallDate BETWEEN '3/26/2009' AND '3/28/2009'

UNION

SELECT CallReceivedID,CallID,CallIDKey,'DBASE4' as DBase
FROM testdatabase4.dbo.tblCallReceived
WHERE CallDate BETWEEN '3/26/2009' AND '3/28/2009'


UNION

SELECT CallReceivedID,CallID,CallIDKey,'DBASE5' as DBase
FROM testdatabase5.dbo.tblCallReceived
WHERE CallDate BETWEEN '3/26/2009' AND '3/28/2009'

That way all your data is in the right columns and you can determine where each bit of information came from and process i accordingly.

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-04-03 : 12:06:21

The union doesn't show up the right way,

SELECT LocalUserId as [name],disposition,'Database1' as Database_name,
COUNT(*) AS Total_Calls
FROM
database1.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId

UNION

SELECT LocalUserId as [name],disposition,'Databas2' as Database_name,
COUNT(*) AS total_calls
FROM
database2.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='Hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
--ORDER BY LocalUserID, Disposition

union

SELECT LocalUserId as [name],disposition,'Database3' as Database_name,
COUNT(*) AS total_calls
FROM
database3.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
ORDER BY LocalUserID, Disposition

Here how it looks like when i run the union query

NAME DISPOSITION DATABASE_NAME TOTAL_CALLS
Jon Hnagup database1 1
Jon Hangup Database2 2
Mike Hangup Database1 1
Mike Hangup database3 4

It should show up like this

Name Disposition Database1 Database2 Database3 Total_Calls
Jon hangup 1 2 0 3
MIke hangup 1 0 4 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:35:21

[code]
SELECT [name], Disposition,
SUM(CASE WHEN Database_name='Database1' THEN Total_Calls ELSE 0 END) AS Database1,
SUM(CASE WHEN Database_name='Database2' THEN Total_Calls ELSE 0 END) AS Database2,
SUM(CASE WHEN Database_name='Database3' THEN Total_Calls ELSE 0 END) AS Database3,
SUM(Total_Calls) AS Total_Calls
FROM
(
SELECT LocalUserId as [name],disposition,'Database1' as Database_name,
COUNT(*) AS Total_Calls
FROM
database1.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId

UNION

SELECT LocalUserId as [name],disposition,'Databas2' as Database_name,
COUNT(*) AS total_calls
FROM
database2.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='Hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
--ORDER BY LocalUserID, Disposition

union

SELECT LocalUserId as [name],disposition,'Database3' as Database_name,
COUNT(*) AS total_calls
FROM
database3.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
)t
ORDER BY [name], Disposition
[/code]

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-04-03 : 16:27:42

Thanks guys it workd now
Really apperciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources