SQL Query- confused with nesting...

**Hugo Kornelis** · Jul 20 '05, 03:30 AM

Re: SQL Query- confused with nesting...

On Tue, 20 Apr 2004 17:21:59 -0500, USI Newsgroups wrote:
[color=blue]
>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!![/color]

(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)

SQL Query- confused with nesting...

SQL Query- confused with nesting...

Comment