CODEWITHSUNDEEP
sqlms-accessdatetime
robert
robert

Reputation: 93

How to I find time elapsed between date time entries in a database

I have been asked to determine how long employees work on a task from a database held in microsoft access. Basically a table contains the employee ID, a task identifier (job number), the status (1 for job started, 2 for job ended), and the date time of the event.

Only one individual may work on any job, and once started they must finish the task before starting another. Entries in the database may look like

EmpID          TaskID        TaskStatus       TaskDate
1              A001          1                15/09/12 09:00
2              A023          1                15/09/12 09:00
1              A001          2                15/09/12 09:30
3              A011          1                15/09/12 09:35
3              A011          2                15/09/12 09:45
1              A016          1                15/09/12 09:45
2              A023          2                15/09/12 09:45
..

I need to produce a report that would show

EmpID          TaskID        Time Take
1              A001          0:30
2              A023          0:45
3              A011          0:10

Further to this I then need to list only those tasks that took less than a given duration (to be used to ensure quality standards are still met on jobs completed quickly).

All help gratefully received.

Upvotes: 2

Views: 224

Answers (2)

HansUp
HansUp

Reputation: 97111

This query will give you the duration in minutes for each TaskID. An uncompleted task (no row with TaskStatus=2) will return Null as the duration. You could use INNER JOIN instead if you prefer to exclude uncompleted tasks.

SELECT
    strt.EmpID,
    strt.TaskID,
    DateDiff('n',strt.TaskDate,stp.TaskDate) AS minutes_taken
FROM
    YourTable AS strt
    LEFT JOIN (
            SELECT TaskID, TaskDate
            FROM YourTable
            WHERE TaskStatus=2
        ) AS stp
    ON strt.TaskID = stp.TaskID
WHERE strt.TaskStatus=1;

Note I used only TaskID for the join condition because "Only one individual may work on any job, and once started they must finish the task before starting another". If my interpretation is wrong, change the ON clause.

Build another query based on that one to filter the results on minutes_taken.

PARAMETERS target Short;
SELECT
    sub.EmpID
    sub.TaskID
    sub.minutes_taken
FROM
    (
        SELECT
            strt.EmpID,
            strt.TaskID,
            DateDiff('n',strt.TaskDate,stp.TaskDate) AS minutes_taken
        FROM
            YourTable AS strt
            LEFT JOIN (
                    SELECT TaskID, TaskDate
                    FROM YourTable
                    WHERE TaskStatus=2
                ) AS stp
            ON strt.TaskID = stp.TaskID
        WHERE strt.TaskStatus=1
    ) AS sub
WHERE sub.minutes_taken < target;

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24076

try something like this:

This is SQL server syntax, do need to convert it to ms access,I think the only thing you need to covert is the cast function, which converts a number to string

 select t1.EmpID,
        t1.TaskID,
        cast(datediff('n',t1.TaskDate,t2.TaskDate)/60 as varchar(10))+' : '+
        cast(datediff('n',t1.TaskDate,t2.TaskDate)%60 as char(2)) as TimeTaken
 from   table1 t1
 inner join   table1 t2
 on     t1.EmpID=t2.EmpID
 and    t1.TaskID=t2.TaskID
 where  t1.TaskStatus=1
 and    t2.TaskStatus=2


SQL fiddle demo

Upvotes: 0

Related Questions