Need Help with a Query

[writhe]

I've got a table of dates and a weekly reference for each date. I need some help writing a SELECT statement that will retrieve the week number based upon the current date/time. For example I'd like to compare the current date/time versus the earliest date/time for each week and get the lowest numbered week that still has a "start date" greater than the current date/time.

Below is what the table looks like.

---------------------------------
| Week  |       DateTime        |	
---------------------------------
|   1   |   9/5/2002 6:30:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 1:00:00 PM
|   1   |   9/8/2002 4:15:00 PM
|   1   |   9/8/2002 4:15:00 PM
|   1   |   9/8/2002 4:15:00 PM
|   1   |   9/8/2002 8:30:00 PM
|   1   |   9/9/2002 9:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM
|   2   |   9/15/2002 1:00:00 PM

[HarryF]

This not good.

---------------------------------
| Week  |       DateTime        |	
---------------------------------
|   1   |   9/5/2002 6:30:00 PM

Think that's going to be tough to work on, depending on what your database is. Ideally you want you date in the db like this;

---------------------------------
| Week  |       DateTime        |	
---------------------------------
|   1   |   2002/09/05 18:30:00
|   x   |   yyyy/mm/dd hh:mm:ss

That's because if you take two days like 05 and 06 September 2002, you'll get this;

2002/09/05 18:30:00
2002/09/06 13:45:00

Strip all the /'s and : and you've got two numbers, like;

20020905183000
20020906134500

Those you can compare with less that < and greater that >.

Your database though may be clever enough to help you out. Depends what it is.

[writhe]

HarryF-

I'm using MS SQL Server 2000. The dates are as you said they should be, they just display differently in Query Analyzer and that's what I made the illustration with.

[HarryF]

Phew! That's lucky. Well in that case you should be able to use normal logic operators to compare dates.

Not sure what MS SQL server can handle query wise, so to calculate what the date was a week ago, you made need to do something in your application to work it out.

The you should be able to run queries like;

"SELECT * FROM table WHERE datetime > '2002/06/05 00:00:00'" to fetch all entries for the last seven days till today 12 June 2002.

Might be worth describing the application and what language you're writing in.

[writhe]

The table I described contains the 2002 NFL schedule. I'm writing an application that will allow users to pick winners each week, week by week. Once the first game of the week has started, you can no longer pick for that week and must pick for the following week. So, I need to compare the current date/time vs. the date/time of the first game for each week. In MS SQL Server, there is a function that will retrieve the current date/time:

GetDate()

returns a date/time in the same format as I what I have in the schedule table, so comparing date/time is not a problem.

If there were a way to select only the earliest game from each week, things would be simpler. I suspect some use of IN or TOP might be needed, but I can't figure it out.

[writhe]

Okay, I'm on the right trail. This is what I was looking for:

SELECT Week, MIN(GameDateTime)
FROM schedule
GROUP BY Week

[writhe]

This doesn't work, but I think something similar to this would do the trick:

SELECT week FROM schedule HAVING GetDate() < (
SELECT Week, MIN(GameDateTime) AS GameDateTime
FROM schedule
GROUP BY Week)

[Zenith]

Those you can compare with less that < and greater that >.

You can compare dates even they're in datetime -format.

-Z-

[writhe]

Hey, everybody, I got it working with some help from SQL Team. Here it is:

SELECT Week AS Week
FROM Schedule
GROUP BY Week
HAVING MIN(GameDateTime) > GETDATE()