Find difference between entryTime and exitTime from the MS Access database

I have a problem in retrieve the difference between EntryTime and Exi开发者_运维知识库tTime. I have a table named IOData and I am working on IOTime column. The format of the table is:

HolderName        IODate        IOTime       IOGateName         IOStatus 
Dinesh Kumar    2010/07/09     00:50:05     Basement(I/O)        Entry 
Dinesh Kumar    2010/07/09     00:52:55     Basement(I/O)        Exit 
Dinesh Kumar    2010/07/09     01:00:07     Basement(I/O)        Entry 
Dinesh Kumar    2010/07/09     01:35:42     Basement(I/O)        Exit 
Dinesh Kumar    2010/07/09     01:36:37     Ground Floor(I/O)    Entry 
Dinesh Kumar    2010/07/09     01:37:02     Ground Floor(I/O)    Exit 
Dinesh Kumar    2010/07/09     01:46:04     Ground Floor(I/O)    Entry 
Dinesh Kumar    2010/07/09     01:46:29     Ground Floor(I/O)    Exit 
Dinesh Kumar    2010/07/09     01:47:02     Basement(I/O)        Entry 
Dinesh Kumar    2010/07/09     04:09:11     Basement(I/O)        Exit 
Dinesh Kumar    2010/07/09     04:09:35     Ground Floor(I/O)    Entry 
Dinesh Kumar    2010/07/09     04:11:27     Ground Floor(I/O)    Exit 
Dinesh Kumar    2010/07/09     04:11:54     Basement(I/O)        Entry 
Dinesh Kumar    2010/07/09     05:10:28     Ground Floor(I/O)    Entry 
Dinesh Kumar    2010/07/09     05:18:12     Main Door(I/O)       Exit 
Dinesh Kumar    2010/07/09     17:55:16     Main Door(I/O)       Entry 
Dinesh Kumar    2010/07/09     17:56:10     Ground Floor(I/O)    Entry 

The problem is I have one column for taking difference of time. How can I overcome this Problem. If I Break IOStatus in two column(Entry and Exit) then status entry can be less then exit or vice ver Any suggestion will be appreciated.

Thanks in advance

I'd create a view.

create view suspicious_person_movements as
select IO.holder_name,
cast(IO.io_date || ' ' || IO.io_time as timestamp) as io_timestamp,
-- Concatenate most recent earlier date and time, and cast to timestamp
cast((select max(io_date) 
      from iodata 
      where holder_name = IO.holder_name 
        and io_date <= IO.io_date) 
     || ' ' ||
     (select max(io_time)
      from iodata
      where holder_name = IO.holder_name 
        and io_date <= IO.io_date 
        and io_time < IO.io_time) as timestamp) as previous_timestamp,
-- Subtract the timestamps to get elapsed_time
cast(IO.io_date || ' ' || IO.io_time as timestamp) - 
cast((select max(io_date) 
      from iodata 
      where holder_name = IO.holder_name 
        and io_date <= IO.io_date) || ' ' ||
     (select max(io_time)
      from iodata
      where holder_name = IO.holder_name 
        and io_date <= IO.io_date 
        and io_time < IO.io_time) as timestamp) as elapsed_time,
IO.io_gate_name,
IO.io_status
from iodata IO
order by holder_name, io_date, io_time  -- Better to sort in the client?

Then I can just select all the rows from suspicious_person_movements. (Lightly edited to reduce horizontal scrolling.)

Dinesh 2010-07-09 00:50:05                                    Basement(I/O)   Entry
Dinesh 2010-07-09 00:52:55   2010-07-09 00:50:05   00:02:50   Basement(I/O)   Exit 
Dinesh 2010-07-09 01:00:07   2010-07-09 00:52:55   00:07:12   Basement(I/O)   Entry
Dinesh 2010-07-09 01:35:42   2010-07-09 01:00:07   00:35:35   Basement(I/O)   Exit 
Dinesh 2010-07-09 01:36:37   2010-07-09 01:35:42   00:00:55   Ground Fl(I/O)  Entry
Dinesh 2010-07-09 01:37:02   2010-07-09 01:36:37   00:00:25   Ground Fl(I/O)  Exit 
Dinesh 2010-07-09 01:46:04   2010-07-09 01:37:02   00:09:02   Ground Fl(I/O)  Entry
Dinesh 2010-07-09 01:46:29   2010-07-09 01:46:04   00:00:25   Ground Fl(I/O)  Exit 
Dinesh 2010-07-09 01:47:02   2010-07-09 01:46:29   00:00:33   Basement(I/O)   Entry
Dinesh 2010-07-09 04:09:11   2010-07-09 01:47:02   02:22:09   Basement(I/O)   Exit 
Dinesh 2010-07-09 04:09:35   2010-07-09 04:09:11   00:00:24   Ground Fl(I/O)  Entry
Dinesh 2010-07-09 04:11:27   2010-07-09 04:09:35   00:01:52   Ground Fl(I/O)  Exit 
Dinesh 2010-07-09 04:11:54   2010-07-09 04:11:27   00:00:27   Basement(I/O)   Entry
Dinesh 2010-07-09 05:10:28   2010-07-09 04:11:54   00:58:34   Ground Fl(I/O)  Entry
Dinesh 2010-07-09 05:18:12   2010-07-09 05:10:28   00:07:44   Main Door(I/O)  Exit 
Dinesh 2010-07-09 17:55:16   2010-07-09 05:18:12   12:37:04   Main Door(I/O)  Entry
Dinesh 2010-07-09 17:56:10   2010-07-09 17:55:16   00:00:54   Ground Fl(I/O)  Entry

I implemented the view in the most obvious way. You'll need indexes on the holder name, date, and time. You might get better performance by joining the table to itself rather than using these scalar subqueries. Either way, performance on a large data set might not be great. (But you can usually restrict the holder name and date range, which will help.)

For Access, you'd use a query more like this, which I copied from SQL view.

SELECT IO.holder_name
     , [io_date] & " " & [io_time] AS io_timestamp
     , (select max(io_date) 
        from iodata 
        where holder_name = IO.holder_name 
          and io_date <= IO.io_date) 
          & " " & 
       (select max(io_time)
        from iodata
        where holder_name = IO.holder_name 
          and io_date <= IO.io_date 
          and io_time < IO.io_time) AS previous_timestamp
     , DateDiff("n",[previous_timestamp],[io_timestamp]) AS elapsed_minutes
     , IO.io_gate_name
     , IO.io_status
FROM iodata AS IO
ORDER BY IO.holder_name, IO.io_date, IO.io_time;

I took some shortcuts, because I'm at work. I display elapsed minutes instead of elapsed time in an "00:00:00" format. I ignore the null timestamp for the first row.

If possible (and I realize it may be waaay too late to do this), I would recommend redesigning your schema. The following seems to make more sense:

HolderName      IOGateName        EnterDT              ExitDT 
Dinesh Kumar    Basement(I/O)     2010/07/09 00:50:05  2010/07/09 00:52:55
Dinesh Kumar    Basement(I/O)     2010/07/09 01:00:07  2010/07/09 01:35:42
Dinesh Kumar    Ground Floor(I/O) 2010/07/09 01:36:37  2010/07/09 01:37:02

Notice that while it may look like less data, you are not actually losing any information...just normalizing your structure.

It will take a bit of care on the data entry side. For instance, you'll need to do UPDATEs in addition to INSERTs. And you may need to handle nested locations if those are possible (eg, a clean room inside a lab where the entry/exit for the clean room would come between entry and exit events for the lab itself).

All of those things can still be handled with this design and the reporting will be greatly simplified and more efficient.

Answering your original question about the duration spent in a location is now trivial.