SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help with a Query

    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.

    Code:
    ---------------------------------
    | 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

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This not good.

    Code:
    ---------------------------------
    | 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;

    Code:
    ---------------------------------
    | 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.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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.

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'm on the right trail. This is what I was looking for:
    Code:
    SELECT Week, MIN(GameDateTime)
    FROM schedule
    GROUP BY Week

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This doesn't work, but I think something similar to this would do the trick:
    Code:
    SELECT week FROM schedule HAVING GetDate() < (
    SELECT Week, MIN(GameDateTime) AS GameDateTime
    FROM schedule
    GROUP BY Week)

  8. #8
    Santos L Halper Zenith's Avatar
    Join Date
    May 2002
    Location
    Finland
    Posts
    641
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those you can compare with less that < and greater that >.
    You can compare dates even they're in datetime -format.

    -Z-

  9. #9
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Houston
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Woo-hoo!

    Hey, everybody, I got it working with some help from SQL Team. Here it is:
    Code:
    SELECT Week AS Week
    FROM Schedule
    GROUP BY Week
    HAVING MIN(GameDateTime) > GETDATE()


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •