CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Belliez
Belliez

Reputation: 5376

SQL Based User Timesheet

I have a table where I have been gathering user's clock in, clock out times and this gets displayed onto a calendar no problem. However, now I would like to see what hours all users are working.

Every time a user Clocks In, a new record is created. Every time a user clocks out, a new record is created also.

My SQL code below allows me to bring up Clock In and Clock Out times:

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
DECLARE @AssumedShiftStartTime DateTime;
DECLARE @AssumedShiftEndTime DateTime;
DECLARE @EmployeeName nvarchar(200);
DECLARE @ShiftStart DateTime
DECLARE @ShiftEnd DateTime


-- Date format: YYYY-MM-DD
SET @StartDate = '2014-07-01 00:00:00'
SET @EndDate = DATEADD (DAY, 1, @StartDate);  -- Add one day
SET @AssumedShiftEndTime = '18:00:00'
SET @AssumedShiftStartTime = '09:00:00'
SET @EmployeeName = 'Paul';

--------------  Get Clock IN / OUT TIMES -----------------
SELECT EmployeeAttendance.LastUpdate, EmployeeAttendance.ClockInTime, EmployeeAttendance.ClockOutTime
FROM            EmployeeAttendance INNER JOIN
                         Membership ON EmployeeAttendance.UserId = Membership.UserId

WHERE EmployeeAttendance.LastUpdate >= @StartDate AND EmployeeAttendance.LastUpdate <= @EndDate
AND Membership.Username = @EmployeeName

Which gives the following results:

    LastUpdate                 ClockInTime                 ClockOutTime
    2014-07-01 08:48:08.650    2014-07-01 08:48:08.650     NULL    
    2014-07-01 18:04:39.943    NULL                        2014-07-01 18:04:39.923
    2014-07-02 08:48:08.680    2014-07-01 09:00:08.340     NULL    
    2014-07-02 18:04:39.343    NULL                        2014-07-01 18:00:39.623
    2014-07-03 08:48:08.620    2014-07-01 08:58:08.860     NULL    
    2014-07-03 18:04:39.455    NULL                        2014-07-01 18:05:39.985

What I am really trying to achieve is something that returns the following results.

EDIT: Where the results return a null, I want to use @AssumedShiftStartTime or @AssumedShiftEndTime to allow a result to be caluclated for total hours but gets difficult because two seperate records are recorded for Clock In and Clock Out:

DATE          CLOCK-IN-TIME    CLOCK-OUT-TIME   TOTAL-HOURS
2014-07-01    08:49            18:04            9 Hours 15 Mins
2014-07-02    09:00            18:00            9 Hours 00 Mins
2014-07-03    08:58            18:05            9 Hours 07 Mins


Total-This-Month
27 Hours 15 Mins

EDIT: Thank you Sean Lange for your help. After applying the help from your reply I get the following output which shows two rows account for Clock In and Clock Out. I am trying to determine how would be the best way to get the results for a single day, calculate total hours, merge next two and calculate hours etc. I think this is more complicated than it needs to be and maybe time for a SQL logic recode?

2014-07-01 08:48:08.650 NULL    NULL    NULL
NULL    2014-07-01 18:04:39.923 NULL    NULL
2014-07-02 08:54:03.483 NULL    NULL    NULL
NULL    2014-07-02 17:09:34.940 NULL    NULL
2014-07-03 08:48:01.070 NULL    NULL    NULL
NULL    2014-07-03 18:12:11.487 NULL    NULL
2014-07-04 08:48:07.983 NULL    NULL    NULL
NULL    2014-07-04 18:07:09.390 NULL    NULL
2014-07-05 08:56:24.410 NULL    NULL    NULL
NULL    2014-07-05 14:19:12.800 NULL    NULL
2014-07-08 08:44:56.727 NULL    NULL    NULL
NULL    2014-07-08 18:15:12.143 NULL    NULL
2014-07-09 08:46:15.103 NULL    NULL    NULL
NULL    2014-07-09 17:10:46.327 NULL    NULL
2014-07-10 08:57:14.733 NULL    NULL    NULL
NULL    2014-07-10 18:10:37.897 NULL    NULL
2014-07-11 08:52:10.783 NULL    NULL    NULL
NULL    2014-07-11 18:08:58.580 NULL    NULL
2014-07-12 08:56:20.073 NULL    NULL    NULL
NULL    2014-07-12 14:15:44.103 NULL    NULL
2014-07-15 08:47:04.330 NULL    NULL    NULL
NULL    2014-07-15 18:10:05.800 NULL    NULL
2014-07-16 08:56:34.490 NULL    NULL    NULL
NULL    2014-07-16 17:05:06.627 NULL    NULL
2014-07-17 08:46:37.263 NULL    NULL    NULL
NULL    2014-07-17 18:06:08.840 NULL    NULL
2014-07-18 08:52:56.200 NULL    NULL    NULL
NULL    2014-07-18 18:11:25.750 NULL    NULL
2014-07-19 08:54:36.277 NULL    NULL    NULL
NULL    2014-07-19 14:15:09.620 NULL    NULL
2014-07-22 08:56:30.623 NULL    NULL    NULL
NULL    2014-07-22 16:03:00.653 NULL    NULL
2014-07-23 08:49:53.687 NULL    NULL    NULL
NULL    2014-07-23 17:07:37.943 NULL    NULL
2014-07-24 08:52:08.690 NULL    NULL    NULL
2014-07-25 08:57:13.477 NULL    NULL    NULL
NULL    2014-07-25 18:09:01.793 NULL    NULL
2014-07-26 08:53:42.597 NULL    NULL    NULL
NULL    2014-07-26 14:03:21.063 NULL    NULL

Any help would be gratefully accepted.

Thank you

Upvotes: 1

Views: 1481

Answers (2)

Kevin Cook
Kevin Cook

Reputation: 1932

Here we make up some test data:

DECLARE @TimeSheet TABLE
(
    EmpId INT,
    LastUpdate DATETIME,
    ClockInTime DATETIME,
    ClockOutTime DATETIME
)

INSERT INTO @TimeSheet
VALUES
(201, '2014-07-01 08:48:08.650',    '2014-07-01 08:48:08.650'   ,NULL                     ),    
(201, '2014-07-01 18:04:39.943',    NULL                        ,'2014-07-01 18:04:39.923'),
(201, '2014-07-02 08:48:08.680',    '2014-07-01 09:00:08.340'   ,NULL                     ),
(201, '2014-07-02 18:04:39.343',    NULL                        ,'2014-07-01 18:00:39.623'),
(201, '2014-07-03 08:48:08.620',    '2014-07-01 08:58:08.860'   ,NULL                     ),
(201, '2014-07-03 18:04:39.455',    NULL                        ,'2014-07-01 18:05:39.985'),
(110, '2014-07-01 08:48:08.650',    '2014-07-01 06:48:08.650'   ,NULL                     ),    
(110, '2014-07-01 18:04:39.943',    NULL                        ,'2014-07-01 14:01:39.923'),
(110, '2014-07-02 08:48:08.680',    '2014-07-01 07:10:08.340'   ,NULL                     ),
(110, '2014-07-02 18:04:39.343',    NULL                        ,'2014-07-01 14:00:39.623'),
(110, '2014-07-03 08:48:08.620',    '2014-07-01 06:58:58.860'   ,NULL                     ),
(110, '2014-07-03 18:04:39.455',    NULL                        ,'2014-07-01 14:01:39.985');

Now lets create a CTE to number our data:

WITH TimeRows AS
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY EmpId, CAST(LastUpdate AS DATE) ORDER BY LastUpdate) RN
    FROM @TimeSheet
)

Now we query the CTE against itself to find our clockin and clockout times:

SELECT T1.EmpId,
    T1.ClockInTime,
    T2.ClockOutTime,
    DATEDIFF(HOUR, T1.ClockInTime, T2.ClockOutTime) AS DHour,
    DATEDIFF(MINUTE, T1.ClockInTime, T2.ClockOutTime) % 60 AS DMinutes
FROM TimeRows T1
INNER JOIN TimeRows T2
    ON T2.EmpId = T1.EmpId
    AND T2.RN = T1.RN + 1
    AND CAST(T2.LastUpdate AS DATE) = CAST(T1.LastUpdate AS DATE)

Here is the output:

EmpId   ClockInTime ClockOutTime    DHour   DMinutes
110 2014-07-01 06:48:08.650 2014-07-01 14:01:39.923 8   13
110 2014-07-01 07:10:08.340 2014-07-01 14:00:39.623 7   50
110 2014-07-01 06:58:58.860 2014-07-01 14:01:39.987 8   3
201 2014-07-01 08:48:08.650 2014-07-01 18:04:39.923 10  16
201 2014-07-01 09:00:08.340 2014-07-01 18:00:39.623 9   0
201 2014-07-01 08:58:08.860 2014-07-01 18:05:39.987 10  7

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33580

Something like this help?

declare @Times table(ClockIn datetime, ClockOut datetime)

insert @Times
select '2014-07-01 08:49', '2014-07-01 18:04' union all
select '2014-07-01 09:00', '2014-07-01 18:00'

select *
    , datediff(hour, ClockIn, ClockOut) - case when datediff(minute, ClockIn, ClockOut) % 60 > 0 then 1 else 0 end as MyHours
    ,datediff(minute, ClockIn, ClockOut) % 60 as MyMinutes
from @Times

Upvotes: 1

Related Questions