CODEWITHSUNDEEP
sqldatetimeformula
Ty.
Ty.

Reputation: 187

SQL Calculate Daily Hours

I have a sql table that has a column called LogicalReaderType that determines if someone is badging in or out for the day. There is another column called BadgeTime which is a timestamp at that moment. I would like to calculate how many hours worked for the day.

Two caveats:

  1. Sometimes people make mistakes and badge out (complete their time for the day) and then come back in the gate without badging in. And badge out again. I would like for the script to be able to make either a best guess or choose the earliest one and then disregard the second badge out.
  2. People do work third shift, so it needs to be smart enough to cross over 12AM and know how to do the math.

Here is an example of one weeks worth of sql output for one employee that I would be working with:

BadgeNumber     LastName    Initials    LogicalReaderType   BadgeTime   EmployeeNumber
153568488433    Doe J   3   41253.32631 54822
153568488433    Doe J   4   41253.68821 54822
153568488433    Doe J   3   41254.3187  54822
153568488433    Doe J   3   41254.31877 54822
153568488433    Doe J   4   41254.72185 54822
153568488433    Doe J   3   41255.32757 54822
153568488433    Doe J   4   41255.58274 54822
153568488433    Doe J   3   41255.6378  54822
153568488433    Doe J   4   41255.71527 54822
153568488433    Doe J   3   41256.31372 54822
153568488433    Doe J   4   41256.55927 54822
153568488433    Doe J   3   41256.56036 54822
153568488433    Doe J   4   41256.69249 54822
153568488433    Doe J   3   41257.3234  54822
153568488433    Doe J   4   41257.37054 54822
153568488433    Doe J   4   41257.69721 54822

As an example output, I would like a table something like this:

In              Out             WorkTime
41253.326308    41253.688206    0.361898
41254.318704                   -41253.318704
41254.318773    41254.721852    0.403079
41255.327569    41255.582743    0.255174
41255.637801    41255.715266    0.077465
41256.313715    41256.559271    0.245556
41256.560359    41256.692488    0.132129
41257.323403    41257.370544    0.047141
                41257.697211    41257.697211

Let me know if I can provide more details.

EDIT: Added some of the other fields that are available.

Upvotes: 0

Views: 219

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need to find the next badge time associated with each out. The most general way of doing this is with a correlated subquery.

Once you have that time, you can then perform logic to get the worktime. The following query takes this approach and assumes that the out time is valid when the worktime is less than 1 day:

select BadgeTime as In,
       (case when NextBadgeTime - BadgetTime < 1 then NextBadgeTime end) as Out,
       (case when NextBadgeTime - BadgetTime < 1 then NextBadgeTime - BadgeTime end) as WorkTime
from (select t.*,
             (select MIN(BadgeTime)
              from t t2
              where t2.BadgeNumber = t.BadgeNumber and
                    t2.LogicalReaderType = 4 and
                    t2.BadgeTime > t.BadgeTime
             ) NextOutTime
      from t
      where LogicalReaderType = 3
     ) t

Upvotes: 2

Related Questions