1

I'm not sure if it's possible to do exactly what I'm wanting, but hopefuly someone here can give me some direction. I'm trying to do some labor reports and a few of them require me to make comparisons to labor by hour.

Starting as simple as possible, I would like to know how I could query over employee In and Out times for a given hour and find out how many total hours have been worked.

For example, if I have:

id   In          Out
--   -------     ------
1    12:00pm      8:30pm
2    12:00pm      8:15pm
3     8:15pm     11:00pm

and I wanted to query for how many hours of labor there were in the 8 o'clock hour then I would expect to see a result of 2 hours (or 120 minutes... 30m + 15m + 45m).

Then moving on from there I'd like to write a query that will show this information for the whole day, but group on the hour showing this same information for each hour. I realize there are other concerns (i.e. I would also need to group on the date, etc.) but if I can figure this out then the rest will be a breeze.

For more context, you can see a similar question I have here: Query for labor cost by hour

(The difference between this question and the other one, if it's not clear, is that I am asking about a specific approach here. If you think you have a better approach to this problem, then please add it to the other question.)

Community
  • 1
  • 1
Brandon Moore
  • 8,590
  • 15
  • 65
  • 120
  • 3
    Are there dates or just times? Can the IN and OUT cross date boundaries? I.E. IN at 11:30PM and OUT at 2:30AM? – Sparky Jan 18 '12 at 17:00
  • They will cross, but for simplicity sake let's assume they don't for now. I can take care of the edge cases after I get the jist of how to do this. – Brandon Moore Jan 18 '12 at 18:02

3 Answers3

1

Try this to get your started, need to tweak it a bit, but should give you what you want on your sample data...

select id,
minutes - case when inMinutes < 0 then 0 else inminutes end as TotalMins
from
(
select id,
case when datediff(mi,'8:00pm',OutTime) >60 then 60 else datediff(mi,'8:00pm',OutTime) end as Minutes,
case when datediff(mi,'8:00pm',InTime) >60 then 60 else datediff(mi,'8:00pm',InTime) end as InMinutes
from testhours
) xx
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • datediff(mi,'8:00pm',OutTime) is always going to be over 60 because it's calculating from the beginning of time (as sql knows it) + 8 hours. Otherwise I think you're on the right track. I just opened another question here on the best way to tackle that date issue if you care to look at it: http://stackoverflow.com/questions/8915364/most-concise-way-to-write-this-query-to-add-hour-to-a-date – Brandon Moore Jan 18 '12 at 18:32
  • Marking this as the answer because it's the best one here and I could have gone this direction (although your answer needs some tweaking). But I ended up doing it a little differently: http://stackoverflow.com/questions/8916447/calculate-how-many-minutes-used-in-a-given-hour – Brandon Moore Jan 19 '12 at 20:56
0

If that is a Microsoft system then datetime values can be converted to double precision floating point numbers where the decimal point divides the date and time like this:

  • integer part: number of days since 1900-01-01
  • decimal part: time in the day (so that 0.5 = 12:00:00)

So you could use something like this:

-- sample datetime values
declare @in datetime, @out datetime
set @in  = '2012-01-18 12:00:00'
set @out = '2012-01-18 20:30:00'

-- calculation
declare @r_in real, @r_out real
set @r_in  = cast(@in as real)
set @r_out = cast(@out as real)

select (24 * (@r_out - @r_in)) as [hours]

BUT it is not that precise, so I would recommend this for calculation:

select cast(datediff(second, @in, @out) as real)/3600 as [hours]
Laszlo T
  • 1,165
  • 10
  • 22
  • Thanks, but you're not quite understanding the problem yet... an employee can clock in/out before/during/after a given hour. How to calculate exactly how many minutes they worked for that hour? I think Sparky is on the right track... – Brandon Moore Jan 18 '12 at 18:07
  • OK, I see. Can I assume that the fields for in and out times are datetime? – Laszlo T Jan 20 '12 at 21:03
  • Yes they are datetime's, but fyi I got a solution to my problem already: http://stackoverflow.com/questions/8916447/calculate-how-many-minutes-used-in-a-given-hour – Brandon Moore Jan 20 '12 at 21:18
0

OK, I would also suggest to use functions because that's maybe a lot slower on large tables but also easier and more simple to code.

But here's another solution without functions:

-- the day you're interested in:
declare @day datetime
set @day = '2012-01-20'

-- sample data
declare @moves table (id int, tin datetime, tout datetime)
insert into @moves values
    (1, '2012-01-20 06:30:00', '2012-01-20 15:45:00'), 
    (2, '2012-01-20 13:05:00', '2012-01-20 19:45:00'), 
    (3, '2012-01-20 10:10:00', '2012-01-20 10:50:00'), 
    (4, '2012-01-20 19:35:00', '2012-01-20 21:00:00')

-- a helper table with hours
declare @i int
declare @hours table (h int, f datetime, t datetime)
set @i = 0
while @i < 24
    begin
    insert into @hours values(@i, dateadd(hour, @i, @day), dateadd(hour, @i + 1, @day))
    set @i = @i + 1
    end

-- here's the code
select h.h, 
    sum(case sign(datediff(second, h.f, m.tin))
        when 1 then 
            case sign(datediff(second, m.tout, h.t))
                when 1 then datediff(minute, m.tin , m.tout)
                else datediff(minute, m.tin , h.t)
                end
        when null then null
        else 
            case sign(datediff(second, m.tout, h.t))
                when 1 then datediff(minute, h.f, m.tout)
                else datediff(minute, h.f, h.t)
                end
        end) as minutesWorked,
        count(distinct m.id) as peopleWorking
    from @hours h inner join @moves m
    -- on h.f >= m.tin and h.t <= m.tout 
    on h.f <= m.tout and h.t >= m.tin
group by h.h
order by h.h

This will give you the following results:

h           minutesWorked peopleWorking
----------- ------------- -------------
6           30            1
7           60            1
8           60            1
9           60            1
10          100           2
11          60            1
12          60            1
13          115           2
14          120           2
15          105           2
16          60            1
17          60            1
18          60            1
19          70            2
20          60            1
21          0             1
Laszlo T
  • 1,165
  • 10
  • 22