Table vinay_hours

id  cico
1   2014-02-20 10:31:00.503
1   2014-02-20 13:00:00.503
1   2014-02-20 15:31:02.503
1   2014-02-20 19:00:02.503
2   2014-02-20 10:00:02.503
2   2014-02-20 18:31:02.503

id is an employee ID and the records are times when an employee swipes a security card.

Expected output

EmployeeId  LoginTime                LogOutTime               WorkingHours
1           2014-02-20 10:31:00.503  2014-02-20 13:00:00.503  3
1           2014-02-20 15:31:02.503  2014-02-20 19:00:02.503  4
2           2014-02-20 10:00:02.503  2014-02-20 18:31:02.503  8

Having done something like this recently, where the door access system tracks badge swipes, and thus the first swipe and last swipe of the day are effectively logging in and logging out, it's fairly straightforward.

Ultimately you need to group the data by date, then get the min and max times for those dates, at which point you can DATEDIFF to the resolution you need.

WITH dataWithDates AS (
    SELECT id, cico, CAST(cico AS DATE) as EventDateOnly FROM vinay_hours
minMaxTimes AS (
    SELECT id, EventDateOnly, MIN(cico) as LoginTime, MAX(cico) as LogoutTime 
        FROM dataWithDates
        GROUP BY id, EventDateOnly
SELECT id, EventDateOnly, DATEDIFF(MINUTE, LoginTime, LogoutTime) FROM minMaxTimes

This uses common table expressions (CTEs), but could easily be written as a set of nested subqueries. I find CTEs help to break the problem down logically, adding to readability; typically the execution plan is identical).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.