CODEWITHSUNDEEP
sqlsql-server
kloodge
kloodge

Reputation: 47

How can I use SQL Server to determine the number of minutes someone worked in every Hour of the day?

I have a Timeclock table that has columns [employeeid], [clock in], and [clock out].

I need to figure out how many minutes they've worked for each HOUR, for example:

Clockin:   11:30
Clockout:  12:37

I need to know you have:

11 hour:  30 min
12 hour:  37 min

Table structure:

|empid(int)|clockin(time(7))|clockout(time(7))|
|       103|        11:37:00|         12:37:00|

Here's my failed attempt:

select 
    hourlist.hour, employeelabor.empposid,
    case 
       when employeelabor.clockin < hourlist.hour 
            and employeelabor.clockout >= dateadd(HH,1,hourlist.hour) 
         then '1:00:00.0'
       when employeelabor.clockin >= hourlist.hour 
            and employeelabor.clockout < dateadd(HH,1,hourlist.hour) 
         then datediff(mi, employeelabor.clockout, employeelabor.clockin)
       when employeelabor.clockin >= hourlist.hour 
            and employeelabor.clockout > dateadd(HH,1,hourlist.hour) 
         then datediff(mi, 1, dateadd(mi, hourlist.hour, 1))
       when employeelabor.clockin < hourlist.hour 
            and employeelabor.clockout < dateadd(HH,1,hourlist.hour) 
         then datediff(mi, hourlist.hour, employeelabor.clockout)
       else 0
     end minworked
from 
    hourlist
JOIN 
    dbo.EmployeeLabor ON (employeelabor.clockin >= hourlist.hour 
                          and employeelabor.clockin < dateadd(HH, 1, hourlist.hour) 
                          or (employeelabor.clockout >= hourlist.hour

                              and employeelabor.clockout < dateadd(HH, 1, hourlist.hour)
order by 
    hourlist.Hour

Upvotes: 0

Views: 151

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

For requirements like these, it helps to have a helper table. In this case, we will create a little @hours helper table, load it with 24 hours, and join this to the Timeclock table to return one record for each employee hour worked:

-- load test data
create table Timeclock(empid int,clockin time,clockout time)
insert into Timeclock values(103,'10:30','12:45')

-- load @hours helper table
declare @hours table (start_time time, end_time time)
declare @hour time = '00:00'
while (select count(*) from @hours) < 24
    begin
        insert into @hours values(@hour, dateadd(hour,1,@hour))
        set @hour = dateadd(hour,1,@hour)
    end

-- get minutes worked per hour per empid
select
    empid,
    datepart(hour,h.start_time) as [hour],
    datediff(minute,
        case
            when t.clockin > h.start_time
            then t.clockin
            else h.start_time
        end,
        case
            when isnull(t.clockout,getdate()) < h.end_time
            then isnull(t.clockout,getdate())
            else h.end_time
        end) as [minutes_worked]
from Timeclock t
    inner join @hours h
        on t.clockin < h.end_time
        and isnull(t.clockout,getdate()) >= h.start_time

Results:

empid       hour        minutes_worked
----------- ----------- --------------
103         10          30
103         11          60
103         12          45

SqlFiddle demo: http://sqlfiddle.com/#!6/b92fe/1

Upvotes: 1

Nasir
Nasir

Reputation: 207

Can we try like that 

declare @tb table
(
    empid int, 
    clockin time(2), 
    clockout time(2)
)

Insert into @tb (empid, clockin, clockout) VALUES (1, DATEADD(mi, 2, GETDATE()),  DATEADD(mi, 20, GETDATE()))
Insert into @tb (empid, clockin, clockout) VALUES (1, DATEADD(mi, -400, GETDATE()),  DATEADD(mi, 25, GETDATE()))
Insert into @tb (empid, clockin, clockout) VALUES (1, '11:37:00',  '12:37:00')

SELECT *, DATEDIFF(mi, clockin, clockout), CONVERT(varchar(5), DATEADD(minute, DATEDIFF(mi, clockin, clockout),  0), 114) FROM @tb

Upvotes: 0

Related Questions