By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Correlated Query Summing Question.

P: 2
I have a query that works just fine, but because I need to use it in Business Objects and I have to use a prompt for the date, I need the initial query for the count as a correlated subquery in the from clause, not in the select statement. I've figured out what I'd like to have, however, I only get one row back (and it is correct!) I need to get the results that the initial query gives me, which is about 10 rows. Is there any way I can do this? (I'm using MS SQL server)

Thanks!!
Susan
(Both queries are below)

Query I know works and am currently using:

select distinct
r.IndividualFirstResolve,
(select sum(n.NumberOfCalls)
from dbo.SolutionCenterTelephone n
where r.IndividualFirstResolve=n.ReceiveCallName
AND substring((convert(char(6),n.calldate,112)),1,7)=
(select max(substring((convert(char(6),k.calldate,112)),1, 7))
from dbo.SolutionCenterTelephone k))
from dbo.HelpDeskTicket r
where r.IndividualFirstResolve in (select distinct e.ReceiveCallName
from dbo.SolutionCenterTelephone e
where department='HelpDesk')


What I'd like to have:

select distinct r.IndividualFirstResolve, ticketId as newcount
from dbo.HelpDeskTicket r, dbo.SolutionCenterTelephone e
where
r.IndividualFirstResolve in (select e.ReceiveCallName
from dbo.SolutionCenterTelephone e
where department='HelpDesk')
AND ticketId in (select sum(n.NumberOfCalls)
from dbo.SolutionCenterTelephone n
where IndividualFirstResolve=n.ReceiveCallName
AND substring((convert(char(6),n.calldate,112)),1,7)=
(select max(substring((convert(char(6),k.calldate,112)),1, 7))
from dbo.SolutionCenterTelephone k))
Apr 25 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.