Solved

select where one attribute has another

Posted on 2007-12-06
4
168 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

21 Experts available now in Live!

Get 1:1 Help Now