469,626 Members | 853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Help in SQL Query - easy

First, let me apologize for how easy this probably is:
DESCR TYPE SELL StartDate EndDate
65048 04 Price A 4/21/2004 4/26/2004
65048 06 Price C 4/20/2004 4/27/2004
65048 08 Price B 4/22/2004 4/28/2004
65049 04 Price A 4/19/2004 4/24/2004
65049 06 Price B 4/22/2004 4/25/2004
65049 09 Price C 4/20/2004 4/29/2004
65050 07 Price A 4/21/2004 4/25/2004
65050 06 Price B 4/18/2004 4/28/2004
65050 05 Price C 4/17/2004 4/29/2004

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

I need a simple query that would display the records with:
Highest TYPE for each DESCR with:
"Date I Enter" >= Startdate
"Date I Enter" <= Enddate

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

I would give you what I have done but it is such a mess I am better off
starting over.

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
3 1671
Try this:

SELECT descr, type, sell, startdate, enddate
FROM SomeTable AS T
WHERE @date_entered BETWEEN startdate AND enddate
AND type =
(SELECT MAX(type)
FROM SomeTable
WHERE @date_entered
BETWEEN startdate AND enddate
AND descr = T.descr);

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
Try this:

SELECT descr, type, sell, startdate, enddate
FROM SomeTable AS T
WHERE @date_entered BETWEEN startdate AND enddate
AND type =
(SELECT MAX(type)
FROM SomeTable
WHERE @date_entered
BETWEEN startdate AND enddate
AND descr = T.descr);

--
David Portas
SQL Server MVP
--
Jul 20 '05 #3
That worked great! Thanks!
"David Portas" <RE****************************@acm.org> wrote in message
news:Tf********************@giganews.com...
Try this:

SELECT descr, type, sell, startdate, enddate
FROM SomeTable AS T
WHERE @date_entered BETWEEN startdate AND enddate
AND type =
(SELECT MAX(type)
FROM SomeTable
WHERE @date_entered
BETWEEN startdate AND enddate
AND descr = T.descr);

--
David Portas
SQL Server MVP
--



-----= 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 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
5 posts views Thread by Steve Patrick | last post: by
11 posts views Thread by troy_lee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.