Counting numbers per hour between a start and end time

Question

I have an attendance management system where start and end times are recorded for each attendance. I can easily calculate the duration of stay by taking start time away from end time.

An example of the data would be like this: -

VisitUID          | AttendeeID   | Start_time              | End_time
0  -----          123  -----     01/01/2015 09:15  -----   01/01/2015 17:15
1  -----          456  -----     01/01/2015 10:45  -----   01/01/2015 16:30
2  -----          753  -----     01/01/2015 08:05  -----   01/01/2015 17:45
3  -----          975  -----     01/01/2015 07:15  -----   01/01/2015 15:05
4  -----          864  -----     01/01/2015 15:55  -----   01/01/2015 16:25
5  -----          246  -----     01/01/2015 16:00  -----   01/01/2015 17:35
6  -----          357  -----     01/01/2015 11:10  -----   01/01/2015 14:55

So a count would be something like this (please excuse me if I have counted wrong!): -

07 - 1
08 - 2
09 - 3
10 - 4
11 - 5
12 - 5
13 - 5
14 - 5
15 - 5
16 - 5
17 - 3

What I am trying to figure out is a way to count how many people were on site in each hour to understand the numbers on site?

The system is SQL and the start and end are datetime values so a way of calculating this in SQL or even Excel would be amazing.

Answer 1

If your data are already in a SQL Server database, it is not necessary to get them out and into Excel. It can be done with just a couple line of SQL. If you prefer Excel, you have some good options as well above or below.

You first need a table with all the possible hours: values(7), (8), ... It can then be joined with your data when the hour from this table is between start and end dates.

Your Data:

declare @date table(VisitUID int, AttendeeID int, Start_time datetime, End_time datetime)
Insert Into @date(VisitUID, AttendeeID, Start_time, End_time) values
    (0, 123, '01/01/2015 09:15', '01/01/2015 17:15')
    , (1, 456, '01/01/2015 10:45', '01/01/2015 16:30')
    , (2, 753, '01/01/2015 08:05', '01/01/2015 17:45')
    , (3, 975, '01/01/2015 07:15', '01/01/2015 15:05')
    , (4, 864, '01/01/2015 15:55', '01/01/2015 16:25')
    , (5, 246, '01/01/2015 16:00', '01/01/2015 17:35')
    , (6, 357, '01/01/2015 11:10', '01/01/2015 14:55')

Query:

Select h.n, COUNT(*)
From (values(7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18)) as h(n)
Inner Join @date d on 
    h.n >= DATEPART(hour, d.Start_time) 
    and  h.n <= DATEPART(hour, d.End_time)+1
Group by h.n

If you need data from 0 to 7 or 18 to 0, just add them to the list: values(6), (7), (8)...(18)...

It seems that your calculation is correct. I have the same output.

Output:

Hour    Count
7       1
8       2
9       3
10      4
11      5
12      5
13      5
14      5
15      5
16      5
17      3

Answer 2

...or even Excel would be amazing.

Assuming your data are in columns A:D, select 11 contiguously vertical cells and paste the following into the Formula Bar and confirm with CONTROL-ENTER:

=SUMPRODUCT((ROWS($1:1)+6>=HOUR(C$2:C$99))*(ROWS($1:1)+6<=HOUR(D$2:D$99)))

Note: if the attendance data extend beyond row 99 then increase the 99s in the formula appropriately.

Answer 3

You can use UNPIVOT in following:

QUERY

;with cte as
(
select   DATEPART(hh,Start_time) StartTime
       , DATEPART(hh,End_time) EndTime
       , CASE WHEN 7 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1  ELSE 0 END AS [7]
       , CASE WHEN 8 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1  ELSE 0 END AS [8]
       , CASE WHEN 9 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1  ELSE 0 END AS [9]
       , CASE WHEN 10 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [10]
       , CASE WHEN 11 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [11]
       , CASE WHEN 12 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [12]
       , CASE WHEN 13 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [13]
       , CASE WHEN 14 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [14]
       , CASE WHEN 15 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [15]
       , CASE WHEN 16 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [16]
       , CASE WHEN 17 BETWEEN DATEPART(hh,Start_time) AND DATEPART(hh,End_time) THEN 1 ELSE 0 END AS [17]
from #test
)
select StartTime1 StartTime, sum(Cnt) Counter
from(
select StartTime,[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17]
from cte
) p
UNPIVOT
(
    Cnt FOR StartTime1 IN ([7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17])
)AS unpvt
group by StartTime1
order by CAST(StartTIme1 AS INT)

SAMPLE DATA

create table #test
(
VisitUID   INT,
AttendeeID INT,
Start_time DATETIME,
End_time   DATETIME,
)
insert into #test values
(0, 123,'01/01/2015 09:15','01/01/2015 17:15'),
(1, 456,'01/01/2015 10:45','01/01/2015 16:30'),
(2, 753,'01/01/2015 08:05','01/01/2015 17:45'),
(3, 975,'01/01/2015 07:15','01/01/2015 15:05'),
(4, 864,'01/01/2015 15:55','01/01/2015 16:25'),
(5, 246,'01/01/2015 16:00','01/01/2015 17:35'),
(6, 357,'01/01/2015 11:10','01/01/2015 14:55')

OUTPUT

StartTime   Counter
7           1
8           2
9           3
10          4
11          5
12          5
13          5
14          5
15          5
16          5
17          3

Answer 4

Excel. For another downvote :)

The formula in F3 copied across and down to suit is:

=--AND(HOUR($C3)<G$2,HOUR($D3)>=F$2)  

The formula in F1 and copied across is:

 =SUM(F3:F1000)

Answer 5

in this picture the following cells contain following formulas / constants ...

F3 ... 01.01.2015 00:00:00

G3 ... =F3+TIME(1;0;0) ... copy this right until AC3

F4 ... =IF(AND(F$3>=$D4;F$3<$E4);1;0) ... copy this right/down until AC10

F12 ... =SUM(F4:F11) ... copy this right until AC12

The essence here is to check if the user login period 'encloses' a point in time you want to test for (e.g. test_time >= start_time AND test_time < end_time) ... whereby you can get philosophic wheter it's >= and < or > and <=

Now ... if you want a daily activity curve without hardcoding the date in the heading, you can have only the time in the header and compare it against the time part of the user login/out dates (as in =date-INT(date) ...)