Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Unanswered: Return row even though search value is null?

    Hi All,

    Here's my problem. I have widgets that have unique ID's, a date that says when they were last moved, and a location. The table's structure doesn't allow me to view when the widget was moved to a certain place, it just specifies it's last movement. For instance, Widget 101 was last moved today, from Operation B. However, I want to know if that Widget was moved TO Operation B today as well. So what I need to find out is whether or not it was at Operation B yesterday or Operation A. So if I could take all the Widget ID's from today (some may have just been created today and may not exist previously), get their dates and then compare to the last Max date moved along with the operation, I would be able to tell if it was moved to Operation B. Here are some examples:

    'Widget 101', 'Operation A', '06-24-05'
    'Widget 101', 'Operation B', '06-24-05'

    'Widget 102', 'Operation B', '06-24-05'
    'Widget 102', 'Operation B', '06-25-05'

    'Widget 103', 'Operation B', '06-01-05'
    'Widget 103', 'Operation B', '06-25-05'

    Now, can I put this into one SQL statement? I've racked my brain for a little bit am stumped. I could write two seperate queries, 1 which retrieves the date last moved for today, and 2 which grabs the max date last moved for each widget that is less than today's date, then write code which matches the two queries up. However, I know it can be simplified into one SQL statement, I just can't figure it out.

    Please let me know if you need more information and thanks so much in advance!
    -Warren

  2. #2
    Join Date
    Jan 2004
    Posts
    51
    Assume the table is as follows
    wip(
    id ,
    location,
    op_date
    )


    select a.id, a.location, a.op_date, b.location as prev_location, b.op_date as prev_date
    from wip a left outer join wip prev on a.id=prev.id
    where a.op_date="today's date"
    and prev.op_date = (select max(op_date)
    from wip c
    where c.op_date < a.op_date
    and c.id=a.op_id
    )

  3. #3
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    AH! So damn simple... I can't believe I overlooked Outer! Let me try it and I'll get back to you...

    Thank you!
    Warren

Posting Permissions

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