Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select where one attribute has another

Posted on 2007-12-06
4
Medium Priority
?
220 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
[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
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
imitchie earned 1400 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

610 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