0

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.

PaulHurleyuk
  • 8,009
  • 15
  • 54
  • 78
  • Do you wanna know if the widget came in or left at the end of period? – LINQ2Vodka Nov 04 '13 at 15:24
  • I want to know if the widget was in the location at any time during the period – PaulHurleyuk Nov 04 '13 at 15:36
  • So you need to find at least 1 record where new location is 101 and date is within period - for every widget ID. Please check update in my answer. – LINQ2Vodka Nov 04 '13 at 15:38
  • 2
    @PaulHurleyuk, Why would you expect 1 for `04-05 Oct 2013` ? if I understand you right then widget 1 was in location 101 from `1-oct` till `3-Oct` – A.B.Cade Nov 04 '13 at 16:09
  • @A.B.Cade, You're totaly right, I'm really sorry ! I've reworded the question to ask for 8th to 9th, so widget 1 was in the location. Thanks for your solution as well, I'll give it a go... – PaulHurleyuk Nov 04 '13 at 16:32

2 Answers2

1

So you need last state of each widget within period.
Probably need subselect statement that selects all widgets between dates, groups them by id, orders by Date desc, selects top 1, so you know widget's last state within the period.

UPDATE according to new conditions

I want to know if the widget was in the location at any time during the period

You make select with distinct IDs and a subselect with EXISTS that checks if the row with the current ID and date within period and new location = X presents in resultset. This will make you know what items came to store at least 1 time.

LINQ2Vodka
  • 2,996
  • 2
  • 27
  • 47
  • that's where I got to, but I don't need the last state. In the data in my question widget 1 doesn't have a transaction during the period, but is moved in, out and back in before the period. I think I need to pair the rows up but I haven't a clue how to do that ? – PaulHurleyuk Nov 04 '13 at 15:24
  • so you need get last state of each widget before date, right? I mean, you specify some "dd-mm-yyyy", select all rows before this date and see last transactions by each id. Is this what you need? – LINQ2Vodka Nov 04 '13 at 15:27
  • Let's clarify your problem... Do you need itams that was at the store all the time within period? Or contact me in skype skvoznik – LINQ2Vodka Nov 04 '13 at 15:28
1

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)

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53