Solved

SQl union query

Posted on 2007-03-29
2
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

'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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

630 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