|
(1) Your logic does not make sense:
- I only want to see the person if their ScheduleStamps exists on or before @DD
- I don't want to see them if they have a ScheduleStamp before and after @DD
I think you only want people who do not have a ScheduleStamp prior to @DD
(2) Find the people who have a schedule stamp prior to @DD
<br />
SELECT tTester.TesterID<br />
FROM tTester<br />
INNER JOIN tResultQue<br />
ON (tTester.TesterID = tResultQue.TesterID)<br />
INNER JOIN tGroup<br />
ON (tResultQue.GroupID = tGroup.GroupID)<br />
WHERE tGroup.ScheduleStamp < @DD<br />
GROUP BY tTester.TesterID<br />
(3) Add it to the WHERE clause
<br />
AND tTester.TesterID NOT IN <br />
(SELECT tTester.TesterID<br />
FROM tTester<br />
INNER JOIN tResultQue<br />
ON (tTester.TesterID = tResultQue.TesterID)<br />
INNER JOIN tGroup<br />
ON (tResultQue.GroupID = tGroup.GroupID)<br />
WHERE tGroup.ScheduleStamp < @DD<br />
GROUP BY tTester.TesterID)<br />
|
|
|
|
|
That's half of it. The problem is, each person can be listed in the table multiple times because they may have multiple ScheduleStamps. I do want to see the person if ScheduleStamp <= @DD, but if they also have one > @DD, I don't want to see them at all.
Thanks again
|
|
|
|
|
That's half of it. The problem is, each person can be listed in the table multiple times because they may have multiple ScheduleStamps. I do want to see the person if ScheduleStamp <= @DD, but if they also have one > @DD, I don't want to see them at all.
Thanks again
P.S. using SQL Server
|
|
|
|
|
(1) Now I think I understand your logic:
Give me everyone who has any ScheduleStamp <= @DD and does not have have a ScheduleStamp > @DD. Your original query is just not eliminating invalid Testers (ScheduleStamp > @DD).
(2) Find the people who have a schedule stamp after to @DD.
SELECT tTester.TesterID
FROM tTester
INNER JOIN tResultQue
ON (tTester.TesterID = tResultQue.TesterID)
INNER JOIN tGroup
ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID
(3) Add it to the WHERE clause.
CREATE PROCEDURE usp_tp_GetPKCall
(
@SexID char,
@RaceID char,
@LBirth datetime,
@UBirth datetime,
@SiteID varchar(5),
@DD datetime
)
AS
IF @SexID = '*' AND
@RaceID <> '*' AND
@SiteID <> '90000'
BEGIN
SELECT
tGroup.ScheduleStamp,
tResultQue.TesterID,
tTester.LastName,
tTester.FirstName,
tTester.WorkNo,
tTester.WorkExtension,
tTester.HomeNo,
tTester.SexID,
tTester.Birth,
tTester.RaceID,
tTester.SiteID
FROM tGroup
INNER JOIN tResultQue
ON (tGroup.GroupID = tResultQue.GroupID)
INNER JOIN tTester
ON (tResultQue.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND
tTester.Birth >= @LBirth AND
tTester.Birth <= @UBirth AND
tTester.SiteID = @SiteID AND
tGroup.ScheduleStamp <= @DD AND
tTester.TesterID NOT IN
(SELECT tTester.TesterID
FROM tTester
INNER JOIN tResultQue
ON (tTester.TesterID = tResultQue.TesterID)
INNER JOIN tGroup
ON (tResultQue.GroupID = tGroup.GroupID)
WHERE tGroup.ScheduleStamp > @DD
GROUP BY tTester.TesterID)
END
GO
|
|
|
|
|
Thank You! Works great! However, there is a follow up here. In the case that a person has more than one ScheduleStamp <= @DD and not > @DD, we will see them listed multiple times. How can I supress the multiples and only see the most recent ScheduleStamp <= @DD for each person?
Thank you for your continued support
|
|
|
|
|
I would normally break a query that is getting this large. It really depends upon the size of your data set and your indexes. Only testing can tell.
Given the current query you can simply add a correlated sub query to the WHERE Clause.
AND tGroup.ScheduleStamp =
(SELECT MAX(cor_tGroup.ScheduleStamp)
FROM tTester cor_tTester
INNER JOIN tResultQue cor_tResultQue
ON (cor_tTester.TesterID = cor_tResultQue.TesterID)
INNER JOIN tGroup cor_tGroup
ON (cor_tResultQue.GroupID = cor_tGroup.GroupID)
WHERE cor_tTester.TesterID = tTester.TestID)
I think I would use a memory temp table to solve the total issue (syntax not tested).
DECLARE @tbl TABLE
(
TesterID INTEGER NOT NULL,
ScheduleStamp DATETIME NOT NULL
)
--*******************************************************
-- find the correct testers and their last ScheduleStamp
--*******************************************************
INSERT INTO @tbl
(
TesterID,
ScheduleStamp
)
SELECT
tTester.TesterID,
MAX(tGroup.ScheduleStamp)
FROM tGroup
INNER JOIN tResultQue
ON (tGroup.GroupID = tResultQue.GroupID)
INNER JOIN tTester
ON (tResultQue.TesterID = tTester.TesterID)
GROUP BY TesterID
HAVING MAX(tGroup.ScheduleStamp) <= @DD
--***************************
-- final select for results
--***************************
SELECT
@tbl.ScheduleStamp,
@tbl.TesterID,
tTester.LastName,
tTester.FirstName,
tTester.WorkNo,
tTester.WorkExtension,
tTester.HomeNo,
tTester.SexID,
tTester.Birth,
tTester.RaceID,
tTester.SiteID
FROM @tbl
INNER JOIN tTester
ON (@tbl.TesterID = tTester.TesterID)
WHERE tTester.RaceID = @RaceID AND
tTester.Birth >= @LBirth AND
tTester.Birth <= @UBirth AND
tTester.SiteID = @SiteID AND
These two queries can even be put into one if you want. I find it easier to maintain this way. Testing for speed might change my mind.
|
|
|
|
|
Great! Thank you for your help....I don't do much on these boards-Is this one of those things where I can give you points? If so, let me know how-you deserve 'em.
|
|
|
|
|
Thanks for the compliment.
I don't know about any points - I just answer questions that intrigue me. I do love working with TSQL.
|
|
|
|
|
|
How do I attach .mdf and .ldf file from SQL Server 2000 to MSDE 2000 ?
Is it possible to attach on prior MSDE version such as MSDE 7.0 ?
Thanks before for your answer.
"Courage choose who will follow, Fate choose who will lead" - Lord Gunner, Septerra Core
"Press any key to continue, where's the ANY key ?" - Homer Simpsons
Drinking gives me amazing powers of insight. I can solve all the worlds problems when drunk, but can never remember the solutions in the morning. - Michael P Butler to Paul Watson on 12/08/03
|
|
|
|
|
did you try sp_attach_db ?
EXEC sp_attach_db @dbname = N'MyDB',
@filename1 = N'C:\Folder\DBData.mdf',
@filename2 = N'C:\Folder\DBLog.ldf'
|
|
|
|
|
Alternatively, you could restore a bckup of the SQL DB to the MSDE DB...have a little app that will allow that if you need it.
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I want to insert a row in a table which has a PK in the first field. I make a INSERT and give parameters to the sql server for all fields except the id field which is set in the db as Autoincrement so i dont have to worry about.
ALL of this is working fine.
But now i want to work immediatly after the insert with the new id of this row, which was given to the row automaticly by the sql-server. My problem now is that i have no Idea how to get the id value into my code (sure I can make a select query questioning all the parameters which i still have from the insertcommand, and ask for the id but this way seems very complicated to me!!!)
So if any more experienced Programmer than me can help me, please!!!
10000 Thanks in advanced.
PS
Languages C#, ADO.Net, and Sql Server 2000
best regards helli
|
|
|
|
|
This works within a stored proceedure. The statement should work in ADO.NET correctly as long as you do not drop your connection or make another call between the INSERT and the SELECT.
<br />
INSERT INTO tbl(ColName1,ColName2)<br />
VALUES('One','Two')<br />
<br />
SELECT SCOPE_IDENTITY()<br />
|
|
|
|
|
Hi, guys
The below is the command I want to install MSDE2000:
setup SAPWD="123456"
But there will be a dialog appearing
SETUP [/?][/f][/i <inf src="" file="">]
...........
........... (....... is the parts I ignore)
And then installation will exit.
How can I install MSDE.
Help, please!!!!
vigorous
|
|
|
|
|
|
this is my first query on this site..
i just wanna optimize a sql query..cud anyone help me out.
This query will give the accounts which are there in table1 & not in table2.
select distinct A.account_id
from table1 A
where not exists
(
SELECT account_id from table2 B
where B.account_id = A.account_id
)
as per the show plan of this query it will do a table scan on table1..
in table2
Using Clustered Index.
Index : acct_id_indx
varun handa
|
|
|
|
|
Do you have any indexes or a primary key on table1?
If you do, do any of them have account_id as the first (or only) column in the index?
Finally, try this and see what it gets you:
SELECT DISTINCT
A.account_id
FROM
table1 A
LEFT OUTER JOIN table2 B
ON B.account_id = A.account_id
WHERE
B.account_id IS NULL
This assumes that account_id is a primary key or other non-nullable column in table2.
There are many ways this query could be written to achieve the same results, but the question as to which ones would actually work, and which one would be most efficient requires much more information than what you've provided.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
thx for ur reply..
but this query is not functioning..
the error generated is::
""the word LEFT OUTER JOIN is not recognized parametr option
incorrect syntax near LEFT""
& this query qill always give me zero results as u r using --where B.account_id is NULL
i m using SQL Advantage
varun handa
|
|
|
|
|
Sorry, I assumed SQL Server 2000, since you didn't specify. I'm not familiar with Sybase.
the LEFT OUTER JOIN tells the query to include ALL records from A, even if they don't have a matching record in B. The B.account_id IS NULL would have caused the query to only return data from rows in A that did NOT have a matching row in B. (When there's no matching row in B, all of the B values would come back as NULL.)
But I'm not sure what the syntax is to get the same effect in Sybase.
My suggestion, then, would be to use your original query, but make sure that table2.account_id is indexed.
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Hi there!
here is my problem:
using a couple of queries, i put 2 datatables in a dataset, now i need to do one more query on those 2 datatables. how can i do this last query without writing the datatables on disk first?
thanks in advance,
fuel2run
|
|
|
|
|
The DataTable class does have a Select method, which runs simple SELECT queries against the DataTable and returns an array of DataRows. I believe this only works with single-table queries, though, so I'm not sure how useful this will be... there's also the DataView component, but IIRC that has the same limitations; it probably just calls the Select method of its DataSource or something.
|
|
|
|
|
do you thinkthere's any way to do the query i need?
basically i don't want to write the 2 temporary tables on disk
any advice?
thanks!
fuel2run
|
|
|
|
|
What query are you doing on what tables, and what temporary tables do you think you need to create? I'm a bit unclear on exactly what you're trying to accomplish other than "run a query on tables in memory which involves creating temporary tables"...
|
|
|
|
|
i need the 2 temporary tables because i'm quering 2 times the same tables and then i'm doing a third query to cross the results
basically, i'm looking for the right bus (it's a bus timetable) to go from some origin to a destination (so i create the temp Table "from", the temp table "TO" and cross them)
it works, but it's horrible and slow
thanks
fuel2run
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
