Qualified Joins

I need a query that compares two tables with times in them and returns only
the common records with the lowest time.
Table1
recordID ArrivalTime
12345 12:01am
12346 12:30am
12347 12:45am
Table2
recordID ArrivalTime
12345 12:03am
12346 12:29am
12347 12:44am
The result should be
recordID ArrivalTime
12345 12:01am
12346 12:29am
12347 12:44am
Can someone point me in the right direction please?Hi Dave,
SELECT recordID,MIN(ArrivalTime)
FROM
(
SELECT recordID,ArrivalTime
FROM TABLE1
UNION
SELECT recordID,ArrivalTime
FROM TABLE2
) SubQuery
GROUP BY recordID
--OR
SELECT
recordID ,
CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
ELSE T2.ArrivalTime END AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
HTH, jens Suessmeyer.|||The 2nd query doesn't handle nulls...
SELECT
COALESCE( T1.recordid , T2.recordid ) AS recordid
, (CASE
WHEN T2.ArrivalTime IS NULL OR T1.ArrivalTime < T2.ArrivalTime ) THEN
T1.ArrivalTime
ELSE T2.ArrivalTime
END) AS ArrivalTime
FROM TABLE1 T1
FULL OUTER JOIN TABLE2 T2
ON T1.recordid = T2.recordid
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131981403.240416.108000@.g47g2000cwa.googlegroups.com...
> Hi Dave,
>
> SELECT recordID,MIN(ArrivalTime)
> FROM
> (
> SELECT recordID,ArrivalTime
> FROM TABLE1
> UNION
> SELECT recordID,ArrivalTime
> FROM TABLE2
> ) SubQuery
> GROUP BY recordID
>
> --OR
> SELECT
> recordID ,
> CASE WHEN T1.ArrivalTime < T2.ArrivalTime THEN T1.ArrivalTime
> ELSE T2.ArrivalTime END AS ArrivalTime
> FROM TABLE1 T1
> FULL OUTER JOIN TABLE2 T2
> ON T1.recordid = T2.recordid
>
> HTH, jens Suessmeyer.
>

q; stored procedure lowest priority

Hello,
I have a long stored procedure that performs some inserts and updates on the
table that users are accessing through a user interface. We this stored
procedure runs users experience slowness, is there any way I can get the
stored procedure runs with a lowest priority so that user will be able to
perform their tasks first.What tasks do you mean? If the sp is triggered from within the page - then
you could palce some conditionals around the code triggering th sp and only
allow the execution , when certain conditions have been met
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> Hello,
> I have a long stored procedure that performs some inserts and updates on
the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>|||Thanks for your reply. Stored procedures are initiated by and Windows
Application. Can you give me example how exactly I can do that?
"Jack Vamvas" wrote:
> What tasks do you mean? If the sp is triggered from within the page - then
> you could palce some conditionals around the code triggering th sp and only
> allow the execution , when certain conditions have been met
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> > Hello,
> >
> > I have a long stored procedure that performs some inserts and updates on
> the
> > table that users are accessing through a user interface. We this stored
> > procedure runs users experience slowness, is there any way I can get the
> > stored procedure runs with a lowest priority so that user will be able to
> > perform their tasks first.
> >
>
>|||JIM.H. wrote:
> Hello,
> I have a long stored procedure that performs some inserts and updates on the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>
You've basically asked the same question about 8 times - why do I have
deadlocking, why does my system run slow, why do I have blocking.
You've been given the same basic answer multiple times - YOUR CODE
AND/OR INDEXES ARE NOT OPTIMIZED.
Your users experience slowness when your procedure is running because
the the updates and inserts are causing blocking. Your goal is simple -
minimize the amount of time that your locks are held. To do that, you
need to thoroughly analyze the queries that you are running, the indexes
that they are using, and make the necessary changes. In one of your
responses to me you basically stated "I don't have time to learn the
right way, I just want a fix". Unfortunately, there is no hidden "magic
setting" that will make everything better.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

q; stored procedure lowest priority

Hello,
I have a long stored procedure that performs some inserts and updates on the
table that users are accessing through a user interface. We this stored
procedure runs users experience slowness, is there any way I can get the
stored procedure runs with a lowest priority so that user will be able to
perform their tasks first.What tasks do you mean? If the sp is triggered from within the page - then
you could palce some conditionals around the code triggering th sp and only
allow the execution , when certain conditions have been met
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> Hello,
> I have a long stored procedure that performs some inserts and updates on
the
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>|||Thanks for your reply. Stored procedures are initiated by and Windows
Application. Can you give me example how exactly I can do that?
"Jack Vamvas" wrote:

> What tasks do you mean? If the sp is triggered from within the page - then
> you could palce some conditionals around the code triggering th sp and onl
y
> allow the execution , when certain conditions have been met
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:6A5E92CA-6EAB-42D9-92F3-6BC57CF45CA5@.microsoft.com...
> the
>
>|||JIM.H. wrote:
> Hello,
> I have a long stored procedure that performs some inserts and updates on t
he
> table that users are accessing through a user interface. We this stored
> procedure runs users experience slowness, is there any way I can get the
> stored procedure runs with a lowest priority so that user will be able to
> perform their tasks first.
>
You've basically asked the same question about 8 times - why do I have
deadlocking, why does my system run slow, why do I have blocking.
You've been given the same basic answer multiple times - YOUR CODE
AND/OR INDEXES ARE NOT OPTIMIZED.
Your users experience slowness when your procedure is running because
the the updates and inserts are causing blocking. Your goal is simple -
minimize the amount of time that your locks are held. To do that, you
need to thoroughly analyze the queries that you are running, the indexes
that they are using, and make the necessary changes. In one of your
responses to me you basically stated "I don't have time to learn the
right way, I just want a fix". Unfortunately, there is no hidden "magic
setting" that will make everything better.
Tracy McKibben
MCDBA
http://www.realsqlguy.com