April 2, 2009 at 12:26 pm
I have 5 differnet databases and all of them have the same table name 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.
Name Disposition TCDatabase1 TCDatabase2 TCdatabase3 Total
test hangup 2 0 3 5
Test2 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
April 3, 2009 at 2:34 pm
I figured it out
here is the sql u have to do just if some 1 has this kind of a question
select name,disposition,sum(TCDatabase1), sum(TCDatabase2),
sum(TCDatabase3) , sum(TCDatabase......
from
(
SELECT LocalUserId as [name],disposition,
COUNT(*) AS TCDatabase1, 0 as TCDatabase2, 0 as TCDatabase3, 0 as
TCDatabase.......
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
union all
SELECT LocalUserId as [name],disposition,
0 AS TCDatabase1, count(*) as TCDatabase2, 0 as TCDatabase3, 0 as
TCDatabase.......
FROM
testdatabase2.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
union all
SELECT LocalUserId as [name],disposition,
0 AS TCDatabase1, 0 as TCDatabase2, count(*) as TCDatabase3, 0 as
TCDatabase.......
FROM
testdatabase3.dbo.tblCallReceived
WHERE
CallDate BETWEEN '3/26/2009' AND '3/28/2009'
and disposition ='hangup' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
) AS rawdata
GROUP BY Disposition, name
ORDER BY name, Disposition
April 6, 2009 at 9:14 am
gr8..thanks for posting the solution..it will surely help others..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply