Need Help with a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help with a Query

Author

Topic

writhe
Starting Member

15 Posts

Posted - 2002-06-12 : 18:39:09

I've got a table with a list of events, each with its own DateTime field. The events are organized by weeks, about 10 or so events per week. I'm trying to write a stored procedure that will compare the current date/time versus the FIRST event for each week and return the week number. The current date/time must be before the "start" of the week, i.e., the earliest event for that week.

This does not work, but I suspect it is not too far off:
SELECT week FROM schedule HAVING GetDate() < (
SELECT Week, MIN(GameDateTime) AS GameDateTime
FROM schedule
GROUP BY Week)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-06-12 : 23:15:03

I think this is what you want

SELECT Week, MIN(GameDateTime) AS GameDateTime
FROM Schedule
WHERE Week >= GETDATE()
GROUP BY Week

writhe
Starting Member

15 Posts

Posted - 2002-06-13 : 10:53:11

ValterBorges-

That's not working. I tried this:

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

But, if I substitute GETDATE() with CONVERT(datetime, '2002-07-01') to test the query out, it still pulls a week number from that week. I need the query to check and see if the current datetime is later than the week's first event, and if so select the next week's number (assuming the current datetime is less than the second week's first event, etc.).

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-13 : 13:14:57

Not sure if I completely understand but try using a having clause:

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

If thats not what you ment please post up your table ddl and a few sample values for me and I'll see what else I can do.

-----------------------
The best answer = just do as rob or page47 say.

writhe
Starting Member

15 Posts

Posted - 2002-06-13 : 14:06:32

M.E.-

I believe that does the trick.

Thank you.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-14 : 10:19:54

Yay, got one right. Scuse me for a sec, I'ma gonna go eat a hero cookie

-----------------------
The best answer = just do as rob or page47 say.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources