473,394 Members | 1,740 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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 1749
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
2
by: edself | last post by:
Greetings, I am semi-new to Access and have a query question. I presume the solution is easy, but need some help. I have created a database with a Contact table. The contact table contains...
5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
13
by: Lee | last post by:
Hello All, First of all I would like to say thank you for all of the help I have received here. I have been teaching myself Access for about 4 years now and I've always been able to find a...
17
by: Liam.M | last post by:
Hey guys, Forgive me if my question my be alittle silly, but I would very much appreciate and assistance that could be given! My situation is as follows: I have created a Button, and set...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
8
by: babyangel43 | last post by:
Hello, I have a query set up in Access. I run it monthly, changing "date of test". I would like this query to be merged with a Word document so that the cover letter is created in Word, the fields...
4
by: n | last post by:
Hello! Here is a problem I hope you can point me to a solution. It Problem: A teacher needs to know which lesson to teach. A school has a curriculum with 26 lessons, A-Z. For a given class,...
11
by: troy_lee | last post by:
I have two fields on a form. These two fields' values are based on an expression and represent a date range. I need to create a SQL statement that will use the returned values of these two fields...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.