Count By Hour

  • Thread starter Thread starter alphamisanthrope
  • Start date Start date
A

alphamisanthrope

Hi, All!

Here's my conundrum. I need a head count by hour.

I have two columns: Time in, Time out. Now what I need is a query which
yields a count of people present by hour. This will be an ongoing
report, 24/7. I'm sure I am making this much more difficult than it
should be. Please help.
 
The first issue is to ensure that you get a record for every hour of every
date, regardless of whether anyone came in during that time or not. The data
has to come from somewhere, so you will need to create a table that holds
all possible entries. You can then use a subquery to count the number of
attendees within that hour.

1. Create another table, with one Date/Time field names (say) DateHour.
Mark it as primary key
Save the table as tblDateHour.
Enter a record for each date and hour in the period you wish to query, or
use the function below to add the dates programmatically.

2. Type an expression like this into a fresh column of the Field row in
query design:
Attendees: CLng(Nz((SELECT Count("*") AS HowMany FROM Table1
WHERE tblDateHour.DateHour Between Table1.TimeIn And Table1.TimeOut),0))

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Here is the function to populate the dates programmatically.
To enter every hour between 1/1/2007 and 12/31/2008, open the Immediate
Window (Ctrl+G), and enter:
? MakeDates(#1/1/2007#, #12/31/2008#)

Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset
Dim lngHour As Long

Set rs = DBEngine(0)(0).OpenRecordset("tblDateHour")
With rs
For dt = dtStart To dtEnd
For lngHour = 0 To 23
.AddNew
!DateHour = DateAdd("h", lngHour, dt)
.Update
Next
Next
End With
rs.Close
Set rs = Nothing
End Function
 
yes, you can make a query out of those fields, but, i guess you only need the
time in field, have it counted then. in your query, press the sum buttom and
on the total, select count.
 
The first issue is to ensure that you get a record for every hour of every
date, regardless of whether anyone came in during that time or not. The data
has to come from somewhere, so you will need to create a table that holds
all possible entries. You can then use a subquery to count the number of
attendees within that hour.
Click to expand...

Allen, I like the idea of using an auxiliary table (usually called
Calendar) but I don't see why a subquery is required. Rather, isn't it
just a simple join? e.g.

SELECT tblDateHour.DateHour, COUNT(*) AS HowMany
FROM Table1, tblDateHour
WHERE tblDateHour.DateHour BETWEEN Table1.TimeIn AND Table1.TimeOut
GROUP BY tblDateHour.DateHour;

Jamie.

--
 
Hi, All!

Here's my conundrum. I need a head count by hour.

I have two columns: Time in, Time out. Now what I need is a query which
yields a count of people present by hour. This will be an ongoing
report, 24/7. I'm sure I am making this much more difficult than it
should be. Please help.
Click to expand...

As an alternative to Allen's suggestion, and if you aren't concerned
about returning a record for every hour if no one was present, you can
try the method given by Douglas Steele in his reply to my post in this
thread:

http://preview.tinyurl.com/yaveee
 
Smartin said:
As an alternative to Allen's suggestion, and if you aren't concerned
about returning a record for every hour if no one was present, you can
try the method given by Douglas Steele in his reply to my post in this
thread:

http://preview.tinyurl.com/yaveee
Click to expand...

If "not returning a record for every hour" were a requirement and the
OP had a single timestamp column, no CROSS JOIN (formerly cartesian
product) is required (as your link implies); rather, you just have to
GROUP BY hour and, unlike in your case (where you had to find values
within an hour of _each other_), we need to find the start of the
_current hour block_ e.g. consider this quick example:

CREATE TABLE Test (
date_col DATETIME NOT NULL
);

INSERT INTO Test (date_col) VALUES (#2007-01-01 09:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 10:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 10:02:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:02:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:03:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 09:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 10:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 10:02:00#);

SELECT DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#), COUNT(*)
FROM test
GROUP BY DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#);

However, unlike you, the OP doesn't have single timestamps; rather, he
has periods i.e. start- and end-date pairs. First, the predicates for
sequenced queries are more complex e.g. this to find the intersection
of other periods for each start date:

CREATE TABLE Test2 (
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date)
)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 09:01:00#, #2007-01-01 09:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 10:01:00#, #2007-01-01 10:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 10:02:00#, #2007-01-01 11:03:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:01:00#, #2007-01-01 11:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:02:00#, #2007-01-01 12:03:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:03:00#, #2007-01-01 13:04:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 09:01:00#, #2007-01-02 09:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 10:01:00#, #2007-01-02 10:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 10:02:00#, #2007-01-02 11:03:00#)
;

SELECT DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#), COUNT(*)
FROM Test2 AS T1, Test2 AS T2
WHERE IIF(DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#) > DATEADD('H', DATEDIFF('H',
#1990-01-01 00:00:00#, T2.start_date), #1990-01-01 00:00:00#),
DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date),
#1990-01-01 00:00:00#), DATEADD('H', DATEDIFF('H', #1990-01-01
00:00:00#, T2.start_date), #1990-01-01 00:00:00#)) < IIF(DATEADD('H',
DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date), #1990-01-01
00:59:59#) > DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T2.end_date), #1990-01-01 00:59:59#), DATEADD('H', DATEDIFF('H',
#1990-01-01 00:00:00#, T2.end_date), #1990-01-01 00:59:59#),
DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date),
#1990-01-01 00:59:59#))
GROUP BY DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#)

Not only are the above predicates more complex, it returns only part of
the result: the OP requires a count for *every* hour period between
start_date and end_date, not just the first hour. In procedural terms
we need to iterate hourly between start_date and end_date. SQL is not a
procedural language; rather it is set based. Therefore, the preferred
solution is to use a set of hourly periods to which we can JOIN, hence
the Calendar table:

CREATE TABLE Sequence (
seq INTEGER NOT NULL UNIQUE
)
;
INSERT INTO Sequence (seq) VALUES (1)
;
INSERT INTO [Sequence] (seq)
SELECT Units.nbr + Tens.nbr
FROM
(
SELECT nbr FROM
(
SELECT 0 AS nbr FROM [Sequence]
UNION ALL
SELECT 1 FROM [Sequence]
UNION ALL
SELECT 2 FROM [Sequence]
UNION ALL
SELECT 3 FROM [Sequence]
UNION ALL
SELECT 4 FROM [Sequence]
UNION ALL
SELECT 5 FROM [Sequence]
UNION ALL
SELECT 6 FROM [Sequence]
UNION ALL
SELECT 7 FROM [Sequence]
UNION ALL
SELECT 8 FROM [Sequence]
UNION ALL
SELECT 9 FROM [Sequence]
) AS Digits
) AS Units,
(
SELECT nbr * 10 AS nbr FROM
(
SELECT 0 AS nbr FROM [Sequence]
UNION ALL
SELECT 1 FROM [Sequence]
UNION ALL
SELECT 2 FROM [Sequence]
UNION ALL
SELECT 3 FROM [Sequence]
UNION ALL
SELECT 4 FROM [Sequence]
UNION ALL
SELECT 5 FROM [Sequence]
UNION ALL
SELECT 6 FROM [Sequence]
UNION ALL
SELECT 7 FROM [Sequence]
UNION ALL
SELECT 8 FROM [Sequence]
UNION ALL
SELECT 9 FROM [Sequence]
) AS Digits
) AS Tens
WHERE Units.nbr + Tens.nbr > 1
;
CREATE TABLE Calendar (
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL
)
;
INSERT INTO Calendar (start_date, end_date) VALUES
(#2007-01-01 00:00:00#, #2007-01-01 00:59:59#)
;
INSERT INTO Calendar (start_date, end_date)
SELECT DATEADD('H', S1.seq, C1.start_date),
DATEADD('H', S1.seq, C1.end_date)
FROM Calendar AS C1, Sequence AS S1
;

After the one-off effort to create the Calendar table, sequenced
queries are easier to write e.g. the OP's required results without
showing the period with a zero count:

SELECT C1.start_date, COUNT(*)
FROM Test2 AS T1, Calendar AS C1
WHERE IIF(DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#) > C1.start_date, DATEADD('H',
DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date), #1990-01-01
00:00:00#), C1.start_date) < IIF(DATEADD('H', DATEDIFF('H', #1990-01-01
00:00:00#, T1.end_date), #1990-01-01 00:59:59#) > C1.end_date,
C1.end_date, DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.end_date), #1990-01-01 00:59:59#))
GROUP BY C1.start_date;

Jamie.

--
 
Jamie said:
Smartin said:
As an alternative to Allen's suggestion, and if you aren't concerned
about returning a record for every hour if no one was present, you can
try the method given by Douglas Steele in his reply to my post in this
thread:

http://preview.tinyurl.com/yaveee
Click to expand...

If "not returning a record for every hour" were a requirement and the
OP had a single timestamp column, no CROSS JOIN (formerly cartesian
product) is required (as your link implies); rather, you just have to
GROUP BY hour and, unlike in your case (where you had to find values
within an hour of _each other_), we need to find the start of the
_current hour block_ e.g. consider this quick example:

CREATE TABLE Test (
date_col DATETIME NOT NULL
);

INSERT INTO Test (date_col) VALUES (#2007-01-01 09:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 10:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 10:02:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:02:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-01 11:03:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 09:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 10:01:00#);
INSERT INTO Test (date_col) VALUES (#2007-01-02 10:02:00#);

SELECT DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#), COUNT(*)
FROM test
GROUP BY DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, date_col),
#1990-01-01 00:00:00#);

However, unlike you, the OP doesn't have single timestamps; rather, he
has periods i.e. start- and end-date pairs. First, the predicates for
sequenced queries are more complex e.g. this to find the intersection
of other periods for each start date:

CREATE TABLE Test2 (
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date)
)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 09:01:00#, #2007-01-01 09:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 10:01:00#, #2007-01-01 10:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 10:02:00#, #2007-01-01 11:03:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:01:00#, #2007-01-01 11:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:02:00#, #2007-01-01 12:03:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-01 11:03:00#, #2007-01-01 13:04:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 09:01:00#, #2007-01-02 09:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 10:01:00#, #2007-01-02 10:02:00#)
;
INSERT INTO Test2 (start_date, end_date)
VALUES (#2007-01-02 10:02:00#, #2007-01-02 11:03:00#)
;

SELECT DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#), COUNT(*)
FROM Test2 AS T1, Test2 AS T2
WHERE IIF(DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#) > DATEADD('H', DATEDIFF('H',
#1990-01-01 00:00:00#, T2.start_date), #1990-01-01 00:00:00#),
DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date),
#1990-01-01 00:00:00#), DATEADD('H', DATEDIFF('H', #1990-01-01
00:00:00#, T2.start_date), #1990-01-01 00:00:00#)) < IIF(DATEADD('H',
DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date), #1990-01-01
00:59:59#) > DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T2.end_date), #1990-01-01 00:59:59#), DATEADD('H', DATEDIFF('H',
#1990-01-01 00:00:00#, T2.end_date), #1990-01-01 00:59:59#),
DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date),
#1990-01-01 00:59:59#))
GROUP BY DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#)

Not only are the above predicates more complex, it returns only part of
the result: the OP requires a count for *every* hour period between
start_date and end_date, not just the first hour. In procedural terms
we need to iterate hourly between start_date and end_date. SQL is not a
procedural language; rather it is set based. Therefore, the preferred
solution is to use a set of hourly periods to which we can JOIN, hence
the Calendar table:

CREATE TABLE Sequence (
seq INTEGER NOT NULL UNIQUE
)
;
INSERT INTO Sequence (seq) VALUES (1)
;
INSERT INTO [Sequence] (seq)
SELECT Units.nbr + Tens.nbr
FROM
(
SELECT nbr FROM
(
SELECT 0 AS nbr FROM [Sequence]
UNION ALL
SELECT 1 FROM [Sequence]
UNION ALL
SELECT 2 FROM [Sequence]
UNION ALL
SELECT 3 FROM [Sequence]
UNION ALL
SELECT 4 FROM [Sequence]
UNION ALL
SELECT 5 FROM [Sequence]
UNION ALL
SELECT 6 FROM [Sequence]
UNION ALL
SELECT 7 FROM [Sequence]
UNION ALL
SELECT 8 FROM [Sequence]
UNION ALL
SELECT 9 FROM [Sequence]
) AS Digits
) AS Units,
(
SELECT nbr * 10 AS nbr FROM
(
SELECT 0 AS nbr FROM [Sequence]
UNION ALL
SELECT 1 FROM [Sequence]
UNION ALL
SELECT 2 FROM [Sequence]
UNION ALL
SELECT 3 FROM [Sequence]
UNION ALL
SELECT 4 FROM [Sequence]
UNION ALL
SELECT 5 FROM [Sequence]
UNION ALL
SELECT 6 FROM [Sequence]
UNION ALL
SELECT 7 FROM [Sequence]
UNION ALL
SELECT 8 FROM [Sequence]
UNION ALL
SELECT 9 FROM [Sequence]
) AS Digits
) AS Tens
WHERE Units.nbr + Tens.nbr > 1
;
CREATE TABLE Calendar (
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL
)
;
INSERT INTO Calendar (start_date, end_date) VALUES
(#2007-01-01 00:00:00#, #2007-01-01 00:59:59#)
;
INSERT INTO Calendar (start_date, end_date)
SELECT DATEADD('H', S1.seq, C1.start_date),
DATEADD('H', S1.seq, C1.end_date)
FROM Calendar AS C1, Sequence AS S1
;

After the one-off effort to create the Calendar table, sequenced
queries are easier to write e.g. the OP's required results without
showing the period with a zero count:

SELECT C1.start_date, COUNT(*)
FROM Test2 AS T1, Calendar AS C1
WHERE IIF(DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.start_date), #1990-01-01 00:00:00#) > C1.start_date, DATEADD('H',
DATEDIFF('H', #1990-01-01 00:00:00#, T1.start_date), #1990-01-01
00:00:00#), C1.start_date) < IIF(DATEADD('H', DATEDIFF('H', #1990-01-01
00:00:00#, T1.end_date), #1990-01-01 00:59:59#) > C1.end_date,
C1.end_date, DATEADD('H', DATEDIFF('H', #1990-01-01 00:00:00#,
T1.end_date), #1990-01-01 00:59:59#))
GROUP BY C1.start_date;

Jamie.
Click to expand...

Thanks for your thorough and enlightening missive Jamie. Your comments
are always appreciated! I am adding this to my own KB.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to count records w/o counting duplicates by week, month, or ye 4
CrossTab: Time Block/interval 2
Help with TOP 10 query please 4
Query not working between Excel and Access 2
no zeroes returned 2
Count how many days ? 6
Counting hours in day 10
Query Error Based on Crosstab Query 3

Back
Top