I have a database that is used to track the location of physical objects, lets call them widgets. It has an audit trail table that tracks when a widget is put in a location, and when it leaves a location (and where it went after that).
So conceptually it looks like this
Widget ID Date Old Location New Location
1 01-Oct-2013 NULL 101
1 03-Oct-2013 101 108
1 08-Oct-2013 108 101
2 01-oct-2013 NULL 101
2 02-Oct-2013 101 103
3 12-oct-2013 NULL 101
I want to be able to query a list of which widgets were in location 101 between a start and end date, such as 08-09 Oct 2013, this should be widget 1 but not widget 2 or 3.
I'm not sure how to get all these cases. I can pull a list of widget's that were moved in before the end, and a list of widgets that were moved out before the start, but that would also eliminate widget 1 as it leaves and comes back.
I think I need to convert this to a table with widget, location, entry date and exit date, but I'm not sure how to do that ?
EDIT: As pointed out, My data was wrong, I've updated to make the question the 8th to 9th (it was the 4th to 5th). So Widget 1 is the only widget in location 101 in that period.
Try something like this:
select *
from
(select "Widget ID" id,
"New Location" loc,
"Date" start_date,
lead("Date", 1, sysdate) over (partition by "Widget ID" order by "Widget ID") end_date
from widgets) t
where t.loc = 101
and start_date < <<your_ending_date>> and end_date > <<your_starting_date>>
here is a sqlfiddle demo (note that I changed you data a little bit)