find missing time periods

[Office User]

I need to find gaps in a series of time periods (ie 8-8:30am or 10-11:00am).
I have a table with the Time Periods listed as well as a query with each
workers date and times for each month. The Unmatched Query won't work
because one day the worker may be "clocked out" from 8-8:30 but another day
may be "clocked out" from 10-11:00.

Here's the SQL of the Unmatched Query I attempted (in case it helps with
field names, etc)

SELECT [Time Periods].Period, [Time Periods].[Start Time]
FROM [Time Periods] LEFT JOIN [Grouped by Date & Time] ON [Time
Periods].[Start Time] = [Grouped by Date & Time].Login
WHERE ((([Grouped by Date & Time].Login) Is Null));

Any insight would be greatly appreciated.
Marcia

 

[Gary Walter]

I need to find gaps in a series of time periods (ie 8-8:30am or
10-11:00am).
I have a table with the Time Periods listed as well as a query with each
workers date and times for each month. The Unmatched Query won't work
because one day the worker may be "clocked out" from 8-8:30 but another
day
may be "clocked out" from 10-11:00.

Here's the SQL of the Unmatched Query I attempted (in case it helps with
field names, etc)

SELECT [Time Periods].Period, [Time Periods].[Start Time]
FROM [Time Periods] LEFT JOIN [Grouped by Date & Time] ON [Time
Periods].[Start Time] = [Grouped by Date & Time].Login
WHERE ((([Grouped by Date & Time].Login) Is Null));

Any insight would be greatly appreciated.
Marcia

Well...you said "*any* insight"....

There are so many ways someone might model
time periods, so please start with describing the
original table that fuels the "query with each workers
date and times for each month." Please give the table
name(s), field names and types, plus some sample
data (which may be bogus, but illustrates what a
"gap" is). Then, please provide the SQL for this
preliminary query ([Grouped by Date & Time])
and results for your sample data.

Then, please present the results you expect from
your unmatched query using your sample data....
make sure your sample data illustrates how "clocked
out" at different times (that caused your unmatched
query not to "work") should work...

{BTW, it is never a good idea to include any punctuation
in an object name, especially a char that is also an operator
like "&". Also, somewhere down the line you may realize
that using spaces in your names just makes bracketing a
pain in the petui and does not really make your model any
more "readable." For example, "GrpByDateTime" would
be a start (although it hardly tells someone what the query
does).}

So...why is the modeling important?

A) A date/time field serves best as a point in time.

B) Elapsed time (gap/period) typically works best as a scalar
(i.e., Long or Integer) where you work with a unit of time
(like number of seconds).

For example, you might have "modeled" your Login as
a Date/Time (which makes sense to me...you are storing
a point in time). Whether you store the date *and the time*
or just the time (so date part will be 12/30/1899), the time
portion will be a fraction of 24 hours and computers have
trouble "matching" fractions (as in equalities of JOINs or
WHERE clauses).

It *might* be that your query simply fails in that respect,
and could be corrected by changing the ON clause to
something like

ON
Format([StartTime],"hh:nn:ss AMPM")
= Format([LogIn],"hh:nn:ss AMPM")

or, if fields contain date as well....

ON
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM")
= Format([LogIn],"mm\-dd\-yyyy hh:nn:ss AMPM")

I'm assuming that table Time Periods was an
enumeration of all possible Start Time's. If so,
it would be easy enough to add one more field
(say "strStartTime"), run an update query that
fills in this field...

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"hh:nn:ss AMPM");

or (if need date as well)

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM");

then, in your "GrpByDateTime" compute
an extra field for a string LogIn (say "strLogIn")

Field: strLogIn: Format([LogIn],"hh:nn:ss AMPM")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then, your unmatched query would join on
the 2 string fields....

===============================
another possible way would depend on how
your [Login] is determined...

if a login is always entered in specific increments of the
hour, i.e., 8:00, 8:15, 8:30, 8:45, 9:00, 9:15,...

then, you can compute the "number of increments"
since midnight for each Login....

then you probably would not even need your enumerated
table (if that is what [Time Periods] is)....

here is where I usually give a solution that typically
ends up having nothing to do with your problem...

so, it would be better if you first reply with requested
info from above...

 

[Office User]

The Time Periods table is set up with 2 fields:
[Period], Number, primary key
[Start Time], Date/Time

table data:
period Start Time
1 7:00:00 AM
2 7:30:00 AM
3 8:00:00 AM
etc in 1/2 hour increments to period 21 at 5:00:00 PM
and Yes it is an enumerated table with all possible times periods throughout
the day.

The system that records call statistics uses these same 1/2 increments for
recording number of calls a worker took as well as which 1/2 hour they
clocked in. It's not perfect but the DB was developed around its
idiosyncrasies.

The Splits table has numerous fields related to call stats. I'll give a few
for illustrative purposes in case it helps give an idea of the info. I only
need to utilize Date and Login for this particular query. Table contains no
primary key.
[Date], Date/Time
[Login], Date/Time
[Split Skill], Text
[ACD Calls], Number (# of calls in each 1/2 hour period)
[ACD Time], Number (time of call recorded in seconds)

some table data:
9/1/2006 10:30:00 AM Operations 1 120
9/1/2006 10:30:00 AM Service Desk 1 45
9/1/2006 11:00:00 AM Operations 0 0
9/1/2006 11:00:00 AM Service Desk 0 0
9/5/2006 12:00:00 PM Operations 1 55
9/5/2006 12:00:00PM Service Desk 1 45

The preliminary query groups the data by date and time period.
SELECT Splits.Date, [Time Periods].Period, Splits.Login
FROM [Time Periods] INNER JOIN Splits ON [Time Periods].[Start Time] =
Splits.Login
GROUP BY Splits.Date, [Time Periods].Period, Splits.Login;

What my "unmatched" query should/needs to return is those time peroids each
day that are missing (i.e. worker was not clocked in). For example, I should
have the following time periods for 9/1 because this worker, who starts at
8:00, came in late:

9/1 8:00
9/1 8:30
9/1 9:00
9/1 9:30
9/1 10:00

Hope the explanation answers your questions and helps clear up, instead of
cause more, confusion.

Thanks,
Marcia

I need to find gaps in a series of time periods (ie 8-8:30am or
10-11:00am).
I have a table with the Time Periods listed as well as a query with each
workers date and times for each month. The Unmatched Query won't work
because one day the worker may be "clocked out" from 8-8:30 but another
day
may be "clocked out" from 10-11:00.

Here's the SQL of the Unmatched Query I attempted (in case it helps with
field names, etc)

SELECT [Time Periods].Period, [Time Periods].[Start Time]
FROM [Time Periods] LEFT JOIN [Grouped by Date & Time] ON [Time
Periods].[Start Time] = [Grouped by Date & Time].Login
WHERE ((([Grouped by Date & Time].Login) Is Null));

Any insight would be greatly appreciated.
Marcia

Well...you said "*any* insight"....

There are so many ways someone might model
time periods, so please start with describing the
original table that fuels the "query with each workers
date and times for each month." Please give the table
name(s), field names and types, plus some sample
data (which may be bogus, but illustrates what a
"gap" is). Then, please provide the SQL for this
preliminary query ([Grouped by Date & Time])
and results for your sample data.

Then, please present the results you expect from
your unmatched query using your sample data....
make sure your sample data illustrates how "clocked
out" at different times (that caused your unmatched
query not to "work") should work...

{BTW, it is never a good idea to include any punctuation
in an object name, especially a char that is also an operator
like "&". Also, somewhere down the line you may realize
that using spaces in your names just makes bracketing a
pain in the petui and does not really make your model any
more "readable." For example, "GrpByDateTime" would
be a start (although it hardly tells someone what the query
does).}

So...why is the modeling important?

A) A date/time field serves best as a point in time.

B) Elapsed time (gap/period) typically works best as a scalar
(i.e., Long or Integer) where you work with a unit of time
(like number of seconds).

For example, you might have "modeled" your Login as
a Date/Time (which makes sense to me...you are storing
a point in time). Whether you store the date *and the time*
or just the time (so date part will be 12/30/1899), the time
portion will be a fraction of 24 hours and computers have
trouble "matching" fractions (as in equalities of JOINs or
WHERE clauses).

It *might* be that your query simply fails in that respect,
and could be corrected by changing the ON clause to
something like

ON
Format([StartTime],"hh:nn:ss AMPM")
= Format([LogIn],"hh:nn:ss AMPM")

or, if fields contain date as well....

ON
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM")
= Format([LogIn],"mm\-dd\-yyyy hh:nn:ss AMPM")

I'm assuming that table Time Periods was an
enumeration of all possible Start Time's. If so,
it would be easy enough to add one more field
(say "strStartTime"), run an update query that
fills in this field...

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"hh:nn:ss AMPM");

or (if need date as well)

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM");

then, in your "GrpByDateTime" compute
an extra field for a string LogIn (say "strLogIn")

Field: strLogIn: Format([LogIn],"hh:nn:ss AMPM")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then, your unmatched query would join on
the 2 string fields....

===============================
another possible way would depend on how
your [Login] is determined...

if a login is always entered in specific increments of the
hour, i.e., 8:00, 8:15, 8:30, 8:45, 9:00, 9:15,...

then, you can compute the "number of increments"
since midnight for each Login....

then you probably would not even need your enumerated
table (if that is what [Time Periods] is)....

here is where I usually give a solution that typically
ends up having nothing to do with your problem...

so, it would be better if you first reply with requested
info from above...

 

[Gary Walter]

okay...it looks like you are missing
User and UserShiftStart in sample data,
but let's look at some alternatives with
the data you have provided.

Alt 1: unmatched query

I believe your unmatched query actually
fails because you do not have a "full enumeration,"
i.e., you did not have a date and period on the
preserve side. Try this...

qryFullEnumeration:

SELECT DISTINCT
Splits.Date,
[Time Periods].period,
[Time Periods].[Start Time]
FROM [Time Periods], Splits;

results in

[Date] period [Start Time]
9/1/2006 1 7:00:00 AM
9/1/2006 2 7:30:00 AM
9/1/2006 3 8:00:00 AM
9/1/2006 4 8:30:00 AM
9/1/2006 5 9:00:00 AM
9/1/2006 6 9:30:00 AM
9/1/2006 7 10:00:00 AM
9/1/2006 8 10:30:00 AM
9/1/2006 9 11:00:00 AM
9/1/2006 10 11:30:00 AM
9/1/2006 11 12:00:00 PM
9/1/2006 12 12:30:00 PM
9/1/2006 13 1:00:00 PM
9/1/2006 14 1:30:00 PM
9/1/2006 15 2:00:00 PM
9/1/2006 16 2:30:00 PM
9/1/2006 17 3:00:00 PM
9/1/2006 18 3:30:00 PM
9/1/2006 19 4:00:00 PM
9/1/2006 20 4:30:00 PM
9/1/2006 21 5:00:00 PM
9/5/2006 1 7:00:00 AM
9/5/2006 2 7:30:00 AM
9/5/2006 3 8:00:00 AM
9/5/2006 4 8:30:00 AM
9/5/2006 5 9:00:00 AM
9/5/2006 6 9:30:00 AM
9/5/2006 7 10:00:00 AM
9/5/2006 8 10:30:00 AM
9/5/2006 9 11:00:00 AM
9/5/2006 10 11:30:00 AM
9/5/2006 11 12:00:00 PM
9/5/2006 12 12:30:00 PM
9/5/2006 13 1:00:00 PM
9/5/2006 14 1:30:00 PM
9/5/2006 15 2:00:00 PM
9/5/2006 16 2:30:00 PM
9/5/2006 17 3:00:00 PM
9/5/2006 18 3:30:00 PM
9/5/2006 19 4:00:00 PM
9/5/2006 20 4:30:00 PM
9/5/2006 21 5:00:00 PM

When you LEFT JOIN the above
to "something," you will have a
[Date] (bad choice of field name
since it is a reserved word in Access),
period, and [Start Time] that is reserved
on the "no match."

For example, "qryUnMatched":

SELECT
qryFullEnumeration.[Date],
qryFullEnumeration.period,
qryFullEnumeration.[Start Time]
FROM
qryFullEnumeration LEFT JOIN Splits
ON
(qryFullEnumeration.Date = Splits.Date)
AND
(qryFullEnumeration.[Start Time] = Splits.Login)
WHERE (((Splits.Login) Is Null));

[Date] period [Start Time]
9/1/2006 1 7:00:00 AM
9/1/2006 2 7:30:00 AM
9/1/2006 3 8:00:00 AM
9/1/2006 4 8:30:00 AM
9/1/2006 5 9:00:00 AM
9/1/2006 6 9:30:00 AM
9/1/2006 7 10:00:00 AM
9/1/2006 10 11:30:00 AM
9/1/2006 11 12:00:00 PM
9/1/2006 12 12:30:00 PM
9/1/2006 13 1:00:00 PM
9/1/2006 14 1:30:00 PM
9/1/2006 15 2:00:00 PM
9/1/2006 16 2:30:00 PM
9/1/2006 17 3:00:00 PM
9/1/2006 18 3:30:00 PM
9/1/2006 19 4:00:00 PM
9/1/2006 20 4:30:00 PM
9/1/2006 21 5:00:00 PM
9/5/2006 1 7:00:00 AM
9/5/2006 2 7:30:00 AM
9/5/2006 3 8:00:00 AM
9/5/2006 4 8:30:00 AM
9/5/2006 5 9:00:00 AM
9/5/2006 6 9:30:00 AM
9/5/2006 7 10:00:00 AM
9/5/2006 8 10:30:00 AM
9/5/2006 9 11:00:00 AM
9/5/2006 10 11:30:00 AM
9/5/2006 12 12:30:00 PM
9/5/2006 13 1:00:00 PM
9/5/2006 14 1:30:00 PM
9/5/2006 15 2:00:00 PM
9/5/2006 16 2:30:00 PM
9/5/2006 17 3:00:00 PM
9/5/2006 18 3:30:00 PM
9/5/2006 19 4:00:00 PM
9/5/2006 20 4:30:00 PM
9/5/2006 21 5:00:00 PM

where you get *all* the gap periods....
only want the "early periods,"
i.e., only periods before first login?

SELECT
q.[Date],
q.period,
q.[Start Time]
FROM
qryUnmatchedPeriods AS q
WHERE
q.[Start Time] <
(SELECT
Min(s.Login)
FROM
Splits As s
WHERE
s.[Date] = q.[Date]);

results in:

[Date] period [Start Time]
9/1/2006 1 7:00:00 AM
9/1/2006 2 7:30:00 AM
9/1/2006 3 8:00:00 AM
9/1/2006 4 8:30:00 AM
9/1/2006 5 9:00:00 AM
9/1/2006 6 9:30:00 AM
9/1/2006 7 10:00:00 AM
9/5/2006 1 7:00:00 AM
9/5/2006 2 7:30:00 AM
9/5/2006 3 8:00:00 AM
9/5/2006 4 8:30:00 AM
9/5/2006 5 9:00:00 AM
9/5/2006 6 9:30:00 AM
9/5/2006 7 10:00:00 AM
9/5/2006 8 10:30:00 AM
9/5/2006 9 11:00:00 AM
9/5/2006 10 11:30:00 AM

Of course, User and ShiftStartTime
are missing in sample data....

In this alternative, I imagine the
full enumeration query will have
to include User and ShiftStartTime,
so "final query" might look like:

SELECT
q.User,
q.ShiftStartTime,
q.[Date],
q.period,
q.[Start Time]
FROM
qryUnmatchedPeriods AS q
WHERE
(
q.[Start Time] <
(SELECT
Min(s.Login)
FROM
Splits As s
WHERE
s.[Date] = q.[Date]
AND
s.User = q.User)
)
AND
(
q.[Start Time] > = q.ShiftStartTime
);

or, all this could have been taken care
of in the full enumeration query possibly,
where instead of using Splits, you use a
preliminary query on Splits that sorts this out ....

The Time Periods table is set up with 2 fields:
[Period], Number, primary key
[Start Time], Date/Time

table data:
period Start Time
1 7:00:00 AM
2 7:30:00 AM
3 8:00:00 AM
etc in 1/2 hour increments to period 21 at 5:00:00 PM
and Yes it is an enumerated table with all possible times periods
throughout
the day.

The system that records call statistics uses these same 1/2 increments for
recording number of calls a worker took as well as which 1/2 hour they
clocked in. It's not perfect but the DB was developed around its
idiosyncrasies.

The Splits table has numerous fields related to call stats. I'll give a
few
for illustrative purposes in case it helps give an idea of the info. I
only
need to utilize Date and Login for this particular query. Table contains
no
primary key.
[Date], Date/Time
[Login], Date/Time
[Split Skill], Text
[ACD Calls], Number (# of calls in each 1/2 hour period)
[ACD Time], Number (time of call recorded in seconds)

some table data:
9/1/2006 10:30:00 AM Operations 1 120
9/1/2006 10:30:00 AM Service Desk 1 45
9/1/2006 11:00:00 AM Operations 0 0
9/1/2006 11:00:00 AM Service Desk 0 0
9/5/2006 12:00:00 PM Operations 1 55
9/5/2006 12:00:00PM Service Desk 1 45

The preliminary query groups the data by date and time period.
SELECT Splits.Date, [Time Periods].Period, Splits.Login
FROM [Time Periods] INNER JOIN Splits ON [Time Periods].[Start Time] =
Splits.Login
GROUP BY Splits.Date, [Time Periods].Period, Splits.Login;

What my "unmatched" query should/needs to return is those time peroids
each
day that are missing (i.e. worker was not clocked in). For example, I
should
have the following time periods for 9/1 because this worker, who starts at
8:00, came in late:

9/1 8:00
9/1 8:30
9/1 9:00
9/1 9:30
9/1 10:00

Hope the explanation answers your questions and helps clear up, instead of
cause more, confusion.

Thanks,
Marcia

I need to find gaps in a series of time periods (ie 8-8:30am or
10-11:00am).
I have a table with the Time Periods listed as well as a query with
each
workers date and times for each month. The Unmatched Query won't work
because one day the worker may be "clocked out" from 8-8:30 but another
day
may be "clocked out" from 10-11:00.

Here's the SQL of the Unmatched Query I attempted (in case it helps
with
field names, etc)

SELECT [Time Periods].Period, [Time Periods].[Start Time]
FROM [Time Periods] LEFT JOIN [Grouped by Date & Time] ON [Time
Periods].[Start Time] = [Grouped by Date & Time].Login
WHERE ((([Grouped by Date & Time].Login) Is Null));

Any insight would be greatly appreciated.
Marcia

Well...you said "*any* insight"....

There are so many ways someone might model
time periods, so please start with describing the
original table that fuels the "query with each workers
date and times for each month." Please give the table
name(s), field names and types, plus some sample
data (which may be bogus, but illustrates what a
"gap" is). Then, please provide the SQL for this
preliminary query ([Grouped by Date & Time])
and results for your sample data.

Then, please present the results you expect from
your unmatched query using your sample data....
make sure your sample data illustrates how "clocked
out" at different times (that caused your unmatched
query not to "work") should work...

{BTW, it is never a good idea to include any punctuation
in an object name, especially a char that is also an operator
like "&". Also, somewhere down the line you may realize
that using spaces in your names just makes bracketing a
pain in the petui and does not really make your model any
more "readable." For example, "GrpByDateTime" would
be a start (although it hardly tells someone what the query
does).}

So...why is the modeling important?

A) A date/time field serves best as a point in time.

B) Elapsed time (gap/period) typically works best as a scalar
(i.e., Long or Integer) where you work with a unit of time
(like number of seconds).

For example, you might have "modeled" your Login as
a Date/Time (which makes sense to me...you are storing
a point in time). Whether you store the date *and the time*
or just the time (so date part will be 12/30/1899), the time
portion will be a fraction of 24 hours and computers have
trouble "matching" fractions (as in equalities of JOINs or
WHERE clauses).

It *might* be that your query simply fails in that respect,
and could be corrected by changing the ON clause to
something like

ON
Format([StartTime],"hh:nn:ss AMPM")
= Format([LogIn],"hh:nn:ss AMPM")

or, if fields contain date as well....

ON
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM")
= Format([LogIn],"mm\-dd\-yyyy hh:nn:ss AMPM")

I'm assuming that table Time Periods was an
enumeration of all possible Start Time's. If so,
it would be easy enough to add one more field
(say "strStartTime"), run an update query that
fills in this field...

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"hh:nn:ss AMPM");

or (if need date as well)

UPDATE
[Time Periods]
SET strStartTime =
Format([StartTime],"mm\-dd\-yyyy hh:nn:ss AMPM");

then, in your "GrpByDateTime" compute
an extra field for a string LogIn (say "strLogIn")

Field: strLogIn: Format([LogIn],"hh:nn:ss AMPM")
Table:
Sort:
Show: <checked>
Criteria:
Or:

then, your unmatched query would join on
the 2 string fields....

===============================
another possible way would depend on how
your [Login] is determined...

if a login is always entered in specific increments of the
hour, i.e., 8:00, 8:15, 8:30, 8:45, 9:00, 9:15,...

then, you can compute the "number of increments"
since midnight for each Login....

then you probably would not even need your enumerated
table (if that is what [Time Periods] is)....

here is where I usually give a solution that typically
ends up having nothing to do with your problem...

so, it would be better if you first reply with requested
info from above...