Solved

select where one attribute has another

Posted on 2007-12-06
4
177 Views
Last Modified: 2012-05-05
this is a weird one, maybe.  i'm in the process writing the procedure below 3 different ways

1., give me the stats on each endpoint, all clients
2., give me the stats on each client, all endpoints
3., give me the stats on each endpoint/client pair  (this is the one posted below)

select b.EndPoint,b.Client,min(a.latency) AS MIN, max(a.latency) AS MAX, AVG(a.latency) AS AVG, stdev(a.latency) AS StDEV
FROM tableA a inner join tableBp b WITH (NOLOCK)
ON a.orderno = b.orderno
GROUP BY endpoint,client

unfortunately, in some cases, there are orderno's without endpoint and/or clients.  what is the most appropriate way to restrict my dataset to only those orderno's with an endpoint AND client ?
0
Comment
Question by:dbaSQL
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 350 total points
ID: 20422196
>>what is the most appropriate way to restrict my dataset to only those orderno's with an endpoint AND client ?

select b.EndPoint,b.Client,min(a.latency) AS MIN, max(a.latency) AS MAX, AVG(a.latency) AS AVG, stdev(a.latency) AS StDEV
FROM tableA a inner join tableBp b WITH (NOLOCK)
ON a.orderno = b.orderno
WHERE b.Client is not null and b.EndPoint is not null
GROUP BY endpoint,client
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20422215
oh gosh.  unreal why i did not think of that.... jeeze
thank you ,imitchie
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20422216
1., give me the stats on each endpoint, all clients

select
  b.EndPoint, min(a.latency) AS MIN, max(a.latency) AS MAX,
  AVG(a.latency) AS AVG, stdev(a.latency) AS StDEV
FROM tableA a
inner join tableBp b WITH (NOLOCK) ON a.orderno = b.orderno
WHERE b.Client is not null and b.EndPoint is not null  -- restricted
GROUP BY b.endpoint

2., give me the stats on each client, all endpoints

select
  b.Client, min(a.latency) AS MIN, max(a.latency) AS MAX,
  AVG(a.latency) AS AVG, stdev(a.latency) AS StDEV
FROM tableA a
inner join tableBp b WITH (NOLOCK) ON a.orderno = b.orderno
WHERE b.Client is not null and b.EndPoint is not null    -- restricted
GROUP BY b.Client

3., give me the stats on each endpoint/client pair  (this is the one posted below)
-- posted above
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 20422221
surely i was overthinking that one.... missing the total obvious
ugh

again, though, thank you very much
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

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. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

23 Experts available now in Live!

Get 1:1 Help Now