SQL Query- confused with nesting...

[USI Newsgroups]

It's me, one more time. My last request didn't quite filter all my records,
I have one more field I need to evaluate in the script.

DESCR TYPE SELL StartDate EndDate Number
65048 04 Price A 4/21/2004 4/26/2004 35456
65048 06 Price C 4/20/2004 4/27/2004 35459
65048 08 Price B 4/22/2004 4/28/2004 34559
65049 04 Price A 4/19/2004 4/24/2004 38595
65049 06 Price B 4/22/2004 4/25/2004 38594
65049 06 Price C 4/20/2004 4/29/2004 38998
65050 07 Price A 4/21/2004 4/25/2004 38112
65050 06 Price B 4/18/2004 4/28/2004 38550
65050 07 Price C 4/17/2004 4/29/2004 38110

Descr, Type, Sell and Number are CHAR
StartDate and EndDate are SmallDatetime

I need a simple query that would display the records with the following
criteria:

#1. "Date I Enter" >= Startdate
#2. "Date I Enter" <= Enddate
#3. Highest TYPE for each DESCR
#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)

Results for ("Date I Enter" = 4/23/2004) should be:
65048 08 Price B 4/22/2004 4/28/2004 34559
65049 09 Price C 4/20/2004 4/29/2004 38998
65050 07 Price A 4/21/2004 4/25/2004 38112

I REALLY appreciate the help!

Thanks!!


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Hugo Kornelis]

On Tue, 20 Apr 2004 17:21:59 -0500, USI Newsgroups wrote:

It's me, one more time. My last request didn't quite filter all my records,
I have one more field I need to evaluate in the script.

DESCR TYPE SELL StartDate EndDate Number
65048 04 Price A 4/21/2004 4/26/2004 35456
65048 06 Price C 4/20/2004 4/27/2004 35459
65048 08 Price B 4/22/2004 4/28/2004 34559
65049 04 Price A 4/19/2004 4/24/2004 38595
65049 06 Price B 4/22/2004 4/25/2004 38594
65049 06 Price C 4/20/2004 4/29/2004 38998
65050 07 Price A 4/21/2004 4/25/2004 38112
65050 06 Price B 4/18/2004 4/28/2004 38550
65050 07 Price C 4/17/2004 4/29/2004 38110

Descr, Type, Sell and Number are CHAR
StartDate and EndDate are SmallDatetime

I need a simple query that would display the records with the following
criteria:

#1. "Date I Enter" >= Startdate
#2. "Date I Enter" <= Enddate
#3. Highest TYPE for each DESCR
#4. Highest NUMBER for each TYPE/DESCR (the tie-breaker)

Results for ("Date I Enter" = 4/23/2004) should be:
65048 08 Price B 4/22/2004 4/28/2004 34559
65049 09 Price C 4/20/2004 4/29/2004 38998
65050 07 Price A 4/21/2004 4/25/2004 38112

I REALLY appreciate the help!

Thanks!!

(Shamelessly stealing David's suggestion as a basis)

SELECT descr, type, sell, startdate, enddate, number
FROM SomeTable AS T
WHERE @date_entered BETWEEN startdate
AND enddate
AND NOT EXISTS
(SELECT *
FROM SomeTable
WHERE @date_entered BETWEEN startdate
AND enddate
AND descr = T.descr
AND ( type > T.type
OR (type = T.type AND number > T.number)))

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)