Solved

SQl union query

Posted on 2007-03-29
2
237 Views
Last Modified: 2010-08-05
If I run the individual queries from the following union, i get the expected result.  However, if I run the union as is, I get an incorrect result.  Can anyone tell me why?  Also, the second query in the union takes 30 seconds to run - why's that?

query:

SELECT
Count(Calls) as calls, sum(booking) as booking, count(calls)/sum(Booking)+ count(calls) as hitrate
FROM

(SELECT
Count(CallLogger.CallLoggerId) AS Calls, 0.0 as booking
FROM
RIO.CallLogger.dbo.CallLogger [CallLogger]
INNER JOIN RIO.CallLogger.dbo.Extensions [Extensions] ON [CallLogger].Extension1 = [Extensions].Extension
WHERE
DateDiff(y, CallLogger.[Date], getdate()) = 0
AND [CallLogger].CallType=1
AND [Extensions].[Group]='Telesales'

UNION

SELECT
0.0 as calls,
SUM(Calls.Booking) AS Booking
FROM
RFVSQL.RFPUB.dbo.Calls [Calls] INNER JOIN RFVSQL.RFPUB.dbo.Users [Users] ON [Calls].UserId = [Users].UserId
WHERE
DateDiff(y, [Calls].DateTimeCreated, getdate()) = 0
AND [Users].DepartmentId=11) X

Thanks
Lapchien
0
Comment
Question by:Lapchien
2 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 18814638
Your entire statement returns 2 for Count(Calls), right?  Is that your problem?

The top-level SELECT should say Sum(Calls).  You counted the calls in the first query & you knew the second section was returning 0 calls.  You want to add the 0 for the 2nd block to the number in the 1st block.

Not sure why the 2nd block takes as long as it does.  How many rows are in [calls]?  For this query, you should have a couple indices:

Create Index IX_CALLS_User_DT on [Calls] (UserID, DateTimeCreated)
go
Create Index IX_Users_User_Dept on [Users] (UserID, DepartmentID)
go

If you already have indices like that, and the tables are not huge it should not take so long.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now