[WarrenM]

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

[sushant]

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
)

[WarrenM]

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