I have a table which consists of the following data.
Employee TimeRegister1 TimeRegister2 TimeRegister3 TimeRegister4
77 2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000
77 2014-04-03 09:48:33.000 2014-04-03 12:13:43.000 2014-04-03 12:22:27.000 2014-04-03 14:03:43.000
181 2014-04-02 08:24:34.000 2014-04-02 13:01:10.000 2014-04-02 14:30:31.000 2014-04-02 18:04:04.000
What I need is to write, on another column, the total calculated from the differences between each pair of columns (odd minus even) for each employee/day.
In the example above, for Employee 77 and 2014-04-01
, it should write on another column the sum of the differences between TimeRegister 2 - TimeRegister 1
, TimeRegister 4 - TimeRegister 3
.
Something like this should be be output (seconds are irrelevant, just need hours/minutes):
Employee TimeRegister1 TimeRegister2 TimeRegister3 TimeRegister4 CALCULATEDCOL
77 2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000 2014-04-01 03:56:00.000
Besides the TimeRegister
Columns can be up to 30 (I just showed 4 columns but could be more) so I need a sequenced calculation for each odd/even pair until it runs out of columns.
Any help how I can achieve this in SQL will be greatly appreciated. Thanks.
Assuming that the table presented is correct (i.e. not 30 columns), you can do this by DATEDIFF
ing the DateTimes, add the differences together, and then adding these back to the base date of one of the times (assuming all DateTimes are on the same day). You can eliminate the seconds by choosing minutes as your diff / add resolution. And to handle missing data, you would require ISNULL()
or COALESCE
on each In / Out
pairing.
SELECT Employee, TimeRegister1, TimeRegister2, TimeRegister3, TimeRegister4,
DATEADD(mi, ISNULL(DATEDIFF(mi, TimeRegister1, TimeRegister2), 0)
+ ISNULL(DATEDIFF(mi, TimeRegister3, TimeRegister4), 0),
CAST(CAST(TimeRegister1 AS DATE) AS DATETIME))
as CALCULATEDCOL
FROM TimeRegister;
As per the comment, it may be better to split the CALCULATEDCOL
into separate date and time components, since as it stands, CALCULATEDCOL
doesn't represent a point in time at all.
SqlFiddle here
If you truly have up to 30 columns, you would need to repeat the calculation for each pair of In/Out
data (and hope that employees don't leave the building more than 15 times / day!).
Edit
To do this from a raw table of clock in / out data, assuming that the direction is obtainable (otherwise the data is next to useless), and discarding any data where the employee hasn't clocked in and out consecutively, on the same day, how about:
WITH cte AS
(
SELECT Employee,
TimeRegister,
Direction,
CAST(TimeRegister AS DATE) AS TheDate,
ROW_NUMBER() OVER (PARTITION BY Employee, CAST(TimeRegister AS DATE)
ORDER BY TimeRegister ASC) AS Rnk
FROM TimeRegister
)
SELECT
cur.Employee,
DATEADD(mi, SUM(DATEDIFF(mi, cur.TimeRegister, nxt.TimeRegister)),
CAST(cur.TheDate AS DATETIME)) AS CALCULATEDCOL
FROM cte cur
INNER JOIN cte nxt
ON cur.Employee = nxt.Employee
AND cur.TheDate = nxt.TheDate AND cur.Rnk + 1 = nxt.Rnk
WHERE cur.Direction = 'I' AND nxt.Direction = 'O'
GROUP BY cur.Employee, cur.TheDate;
SqlFiddle here