469,088 Members | 1,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,088 developers. It's quick & easy.

SQL Query- confused with nesting...

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! =-----
Jul 20 '05 #1
1 1456
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)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by jaysonsch | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.