Solved

Sql Syntax Help

Posted on 2008-06-20
2
290 Views
Last Modified: 2010-03-20
Hello, please reffer to my question here:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23494097.html

I chose to use:
select s.serverip, p.serverid, isnull(p.processCnt ,0) processCnt
from servers s left outer join
( Select serverid, count(1) processCnt from processqueue
Group by serverid) as p
on s.serverid = p.serverid And s.status = 100
and p.processCnt <= s.capacity
order by p.processCnt

But, I found an issue with it after I closed the question.  The problem is that p.serverid is returning null when p.processCnt  returns zero.  I need it to return the ServerId and not a null value. How can I fix this?

Thanks Very Much!
0
Comment
Question by:grogo21
2 Comments
 
LVL 2

Assisted Solution

by:jgoeders
jgoeders earned 100 total points
ID: 21835326
Hmmm....it seems that your sub-query p is counting records.  If the count is 0 that means there are no records.  How will it get a serverID out of no records?
0
 

Accepted Solution

by:
grogo21 earned 0 total points
ID: 21835586
Solution:

select s.serverip, s.serverid, count(*) as processes
from servers s left outer join processqueue p on s.serverid = p.serverid
where s.status = 100
group by s.serverid, p.serverid, s.capacity, s.serverip
having count(*) < s.capacity
order by count(*)

Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

896 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

12 Experts available now in Live!

Get 1:1 Help Now