Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    15

    Unanswered: Selecting "date before" multiple dates

    Hi everyone, happy president's day!

    I am having difficulty selecting the "date before" multiple events. I will create a fictitious database (abbreviated from full formats and honoring my absolute pain from my recent completion of the Austin Marathon) to explain my problem:

    Date Event

    1/02/09 Ran 5 miles
    1/05/09 Ran 8 miles
    1/15/09 Slept
    1/18/09 Ran 5 miles
    1/20/09 Slept
    1/21/09 Ran 3 miles
    1/22/09 Ran 10 miles
    1/24/09 Slept

    What I want to do is return the date and event occurring the entry before each time I slept.

    I have a query of "when I slept" which would reveal the dates:
    1/15/09
    1/20/09
    1/24/09

    What I need help figuring out the SQL code for is a query that retrieves the following out of the database:
    1/05/09 Ran 8 miles
    1/18/09 Ran 5 Miles
    1/22/09 Ran 10 miles

    The ultimate idea, on the next step, would be to determine the amount of time between when I slept and when I last ran (and know the number of miles).

    Did I explain it well? I've been struggling with this for a short while and tried some very tricky Top X entries of descending order but inevitably could not get the query to give me, in the case of the example, 3 distinct answers for the 3 distinct times I've slept.


    And no, the database has nothing to do with running or sleeping. Those are just things that I did too much and didn't do enough. The actual database is sadly more boring.

    Thank you!
    Chris

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by cwebbe2
    What I need help figuring out the SQL code for is a query that retrieves the following out of the database:
    It think this should work :
    Code:
    select  t3.*, datediff( temp_tab.date_field, temp_tab.prev_date )
    from    MyTable t3,
            (       select  t1.date_field, max(t2.date_field) as prev_date
                    from    MyTable t1, MyTable t2
                    where   t1.activity='Slept'
                            and t2.date_field < t1.date_field
                    group by t1.date_field
            ) temp_tab
    where   t3.date_field = temp_tab.prev_date
    If you're anything like us then you'll probably find you sleep every day, usually at night, unless you've reached my age in which case you might find a short sleep in the afternoon is also helpful.

    Quote Originally Posted by cwebbe2
    The ultimate idea, on the next step, would be to determine the amount of time between when I slept and when I last ran (and know the number of miles).
    It would be better if you put the distance ran into a separate field so it can easily be referenced. To get the number of days after running before sleeping then just change the first line above to something like :
    Code:
    select  t3.*, datediff( temp_tab.date_field, temp_tab.prev_date ) as days

  3. #3
    Join Date
    Feb 2009
    Posts
    15
    Your solution worked like a magic trick! After hours and hours of getting the wrong answers, to have it work so well.... thank you, Mike_Bike_Kite Copperfield, thank you very much!

Posting Permissions

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