Finding a range of time

[me1258]

This will be hard to explain.

I have an access program that will log punch in and punch out times.
I need to be able to create a new query or what ever to search for a how many people are punched in at a certain time over a given spread of dates.
example

the user wants to enter 13:00 the and the dates 01/01/2004 thru 01/30/2004
The program should search the database to see how many employees were punched in at 13:00 between those dates.

I thought is would be something like this

their search time was a variable called XTime


If XTime is > PunchInTime AND < PunchOutTime Then
count +1

I am unsure of how to do this if in a module or in a query or how the syntex will work. What I am trying to do is make a graph showing the peak times employees are at work. Not just punch in times.
Hope I explained this well enough
Thaks

 

[JonFer]

Can you post some sample records? It would help to know if the fields are date/times or just time values.

 

[MichaelRed]

I would agree that more info is needed.

Does the data cover 'shifts', such that the timein / time out might be on diffrerent dates?

Does the data include incomplete records (e.g. Timin is recorded but no timeout value present?

Do you really want to enter a value, per your 'example' or is the real goal to generate the stastical info when are the most (least median average) number of people logged in or do you want a graph of 'attendance'?

Ambigious questions usually get ambigous responses.





MichaelRed
mlred@verizon.net

 

[me1258]

The punch in and punch out times are full date time fields.
I have already done the date diff thing in another field. So now I have a punch in time field with a full date time stamp and a punch out field with a full date time stamp.

what I want to accomplish is an attendance graph at a given time of day over a given range of dates. Example.
I want to know how many employees were punched in at 1300 over a specified range of dates. This would include any person punched in before 1300 but has not yet punched out.
I want to end up with a graph that shows me in 1/2 hr incriments how many people were punched in at a given hour over a range of dates

Thanks

 

[JonFer]

I assumed your punch in and punch out times are during the same day. Create a table called TimeChart with each row containing a time of the day that you want to chart in a DateTime field called HourMin.

Join TimeChart to your table like this:

Select DateValue(PunchIn)+HourMin as InTime, Count(*) as CountIn
From YourTable, TimeChart
Where HourMin between TimeValue(PunchIn) and TimeValue(PunchOut)
Group By DateValue(PunchIn)+HourMin

If punch in/out dates can span midnight, you'll need to add some IIF statements in the WHERE clause.

 

[OhioSteve]

I have the answer you are seeking. I recently worked on a meeting scheduling system. We had to answer the question "Will employee X be available for a meeting with the start time A and the end time B?"

To find the answer, we compared the proposed meeting's start time and end time to the start time and end time for each of his existing meetings. When we did each comparison, we asked "Is this meeting's start time after the proposed meeting's end time, or is this meeting's end time prior to the proposed meeting's start time?" if the answer was "yes" for each comparison, then the employee had no conflicts, and he could participate in the proposed meeting. If you are a programmer then I'm sure you can imagine how we wrote the code for this.

You are asking the opposite question. You want to know how many employees were present during a certain time period. You may think that you can put the word "not" in front of each of the tests above, and get the answer. Unfortunately, logic does not work that way. So don't do that. However, you can use another variation on my system to get your answer.

Let's assume that you have three fields: start time, end time, and name. Start time and end time are date/time fields with the "general date" format. This is the data:

timeIn timeOut name
2/1/2003 8:05:00 AM 2/1/2003 6:10:00 PM Joe
2/2/2003 9:00:00 AM 2/2/2003 11:00:00 AM Mary
2/3/2003 10:00:00 PM 2/4/2003 2:00:00 AM Joe
2/2/2003 4:00:00 PM 2/2/2003 11:30:00 PM Robert

You would use this sql syntax to determine who was logged in during a certain time period:

SELECT
myTable.timeIn,
myTable.timeOut,
myTable.name,
IIf([myTable]![timeIn]>#2/3/2003 23:0:0# Or [myTable]![timeOut]<#2/2/2003 22:0:0#,"not timed in","timed in") AS x
FROM myTable;

The output from this query returns x as "timed in" for Robert's shift and for Joe's second shift.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmm ... Still not really answering ALL the questions. I do not think it is a "good" idea to attempt to chart the attendance to the 'minute'. Most orgs go on at least a quarter hour basis, so I have cobbled together a rather crude approximation of a process:

I generated a pseudo time-ckock table for 50 pseudo employees for a month. The ONLY info in the table is:

EmpId as String [11](a pseudo SSN)
dtChkIn as Date
dtChkOut as Date

I populated the table with a In and out time for each EmpId for Each Date in my pseudo Month. This resultes in 1500 records (50 employees over 30 days). All [dtChkIn] items are restricted to between 7 and 10 AM, while [dtChkOut] is restricted to [dtChkIn] or 10 PM. This (obviously?) gives some rather strange work habits / scheduals - but suffuces to illustrate the approach. A BRIEF sample (a query Showing One Employee:

EmpId	dtChkIn	dtChkOut
136-14-1026	8/1/2004 8:25:13 AM	8/1/2004 2:19:44 PM
136-14-1026	8/2/2004 8:25:13 AM	8/2/2004 2:19:44 PM
136-14-1026	8/3/2004 8:25:13 AM	8/3/2004 2:19:44 PM
136-14-1026	8/4/2004 8:25:13 AM	8/4/2004 2:19:44 PM
136-14-1026	8/5/2004 8:25:13 AM	8/5/2004 2:19:44 PM
136-14-1026	8/6/2004 8:25:13 AM	8/6/2004 2:19:44 PM
136-14-1026	8/7/2004 8:25:13 AM	8/7/2004 2:19:44 PM
136-14-1026	8/8/2004 8:25:13 AM	8/8/2004 2:19:44 PM
136-14-1026	8/9/2004 8:25:13 AM	8/9/2004 2:19:44 PM
136-14-1026	8/10/2004 8:25:13 AM	8/10/2004 2:19:44 PM
136-14-1026	8/11/2004 8:25:13 AM	8/11/2004 2:19:44 PM
136-14-1026	8/12/2004 8:25:13 AM	8/12/2004 2:19:44 PM
136-14-1026	8/13/2004 8:25:13 AM	8/13/2004 2:19:44 PM
136-14-1026	8/14/2004 8:25:13 AM	8/14/2004 2:19:44 PM
136-14-1026	8/15/2004 8:25:13 AM	8/15/2004 2:19:44 PM
136-14-1026	8/16/2004 8:25:13 AM	8/16/2004 2:19:44 PM
136-14-1026	8/17/2004 8:25:13 AM	8/17/2004 2:19:44 PM
136-14-1026	8/18/2004 8:25:13 AM	8/18/2004 2:19:44 PM
136-14-1026	8/19/2004 8:25:13 AM	8/19/2004 2:19:44 PM
136-14-1026	8/20/2004 8:25:13 AM	8/20/2004 2:19:44 PM
136-14-1026	8/21/2004 8:25:13 AM	8/21/2004 2:19:44 PM
136-14-1026	8/22/2004 8:25:13 AM	8/22/2004 2:19:44 PM
136-14-1026	8/23/2004 8:25:13 AM	8/23/2004 2:19:44 PM
136-14-1026	8/24/2004 8:25:13 AM	8/24/2004 2:19:44 PM
136-14-1026	8/25/2004 8:25:13 AM	8/25/2004 2:19:44 PM
136-14-1026	8/26/2004 8:25:13 AM	8/26/2004 2:19:44 PM
136-14-1026	8/27/2004 8:25:13 AM	8/27/2004 2:19:44 PM
136-14-1026	8/28/2004 8:25:13 AM	8/28/2004 2:19:44 PM
136-14-1026	8/29/2004 8:25:13 AM	8/29/2004 2:19:44 PM
136-14-1026	8/30/2004 8:25:13 AM	8/30/2004 2:19:44 PM
136-14-1026	8/31/2004 8:25:13 AM	8/31/2004 2:19:44 PM

I then created a query:

SELECT tblTmClck.EmpId, Format([dtChkIn],"Short Date") AS MyDt, CLng(([dtChkIn]-CLng([dtChkIn]))*1440) AS TmIn, Partition([TmIn],420,1320,15) AS TimSlotIn, CLng(CDbl([dtChkOut]-Int([dtChkOut]))*1440) AS TmOut, Partition([TmOut],420,1320,15) AS TimSlotOut
FROM tblTmClck;

to 'redisplay' the information in a couple of different ways;

1)[tab]Isolate the DATE from the chkIn and chkout times (Format([dtChkIn],"Short Date") AS MyDt)

2)[tab]Calculate and display the Minute within the day (CLng(([dtChkIn]-CLng([dtChkIn]))*1440) AS TmIn) and the corresponding value for ChkOut

A3)[tab]nd Finally a Partition function to capture the "time Slot" of the checkIn (and out) to the traditional 15 minute interval (Partition([TmIn],420,1320,15) AS TimSlotIn) as well as well as the coresponding Expression for ChkOut.

Again, a brief Sample for our selectd employee:

EmpId	MyDt	TmIn	TimSlotIn	TmOut	TimSlotOut
136-14-1026	8/1/2004	505	 495: 509	860	 855: 869
136-14-1026	8/2/2004	505	 495: 509	860	 855: 869
136-14-1026	8/3/2004	505	 495: 509	860	 855: 869
136-14-1026	8/4/2004	505	 495: 509	860	 855: 869
136-14-1026	8/5/2004	505	 495: 509	860	 855: 869
136-14-1026	8/6/2004	505	 495: 509	860	 855: 869
136-14-1026	8/7/2004	505	 495: 509	860	 855: 869
136-14-1026	8/8/2004	505	 495: 509	860	 855: 869
136-14-1026	8/9/2004	505	 495: 509	860	 855: 869
136-14-1026	8/10/2004	505	 495: 509	860	 855: 869
136-14-1026	8/11/2004	505	 495: 509	860	 855: 869
136-14-1026	8/12/2004	505	 495: 509	860	 855: 869
136-14-1026	8/13/2004	505	 495: 509	860	 855: 869
136-14-1026	8/14/2004	505	 495: 509	860	 855: 869
136-14-1026	8/15/2004	505	 495: 509	860	 855: 869
136-14-1026	8/16/2004	505	 495: 509	860	 855: 869
136-14-1026	8/17/2004	505	 495: 509	860	 855: 869
136-14-1026	8/18/2004	505	 495: 509	860	 855: 869
136-14-1026	8/19/2004	505	 495: 509	860	 855: 869
136-14-1026	8/20/2004	505	 495: 509	860	 855: 869
136-14-1026	8/21/2004	505	 495: 509	860	 855: 869
136-14-1026	8/22/2004	505	 495: 509	860	 855: 869
136-14-1026	8/23/2004	505	 495: 509	860	 855: 869
136-14-1026	8/24/2004	505	 495: 509	860	 855: 869
136-14-1026	8/25/2004	505	 495: 509	860	 855: 869
136-14-1026	8/26/2004	505	 495: 509	860	 855: 869
136-14-1026	8/27/2004	505	 495: 509	860	 855: 869
136-14-1026	8/28/2004	505	 495: 509	860	 855: 869
136-14-1026	8/29/2004	505	 495: 509	860	 855: 869
136-14-1026	8/30/2004	505	 495: 509	860	 855: 869
136-14-1026	8/31/2004	505	 495: 509	860	 855: 869

4)[tab]Finally, a simple XTab Query (for me, based on the TimeSlot) shows the Attendance for each Date by Time Slot.

The data is badly generated and the results NOT what was requested, even for the limited information provided, but it is perhaps useful for those persuing the concept.

The Process produnces only thirty nine records of 34 columns, which could be used as the basis of an attendance chart. In particular, the use of the partition function (see the ubiquitoue {F1} --- aka HELP for deatils, generates the time slots relatively painlessly, but is opnly available in recent versions of Ms. A. Better (more normally distribuited) data would certainly change the number of records, however this effect should not have a noticable impact on the performance of the process outlined here.

A more complete treatment, to more closely achieve the original goal would need to consider the chkIn and chkout times (or slots) in some additional detail, but is not conceptually difficult.




MichaelRed
mlred@verizon.net

 

[OhioSteve]

The core issue is to compare each value in the recordset to the user's boundary values. If the proposed end time is prior to the existing start time, or if the proposed start time is after the existing end time, then the time periods do not overlap.

My rule assumes that the proposed start time precedes the proposed end time, and the existing start time precedes the existing end time. For example, you can't let the user input "1/1/2000 7:00:00 AM" for the start time and "1/1/2000 6:00 AM" for the end time.

 

[me1258]

WOW you guys/gals are awsome.
What a response. Thank you all for the time you obviously put in to this. I will review these carefully and let you know if I need any additonal help...thanks.


Thanks again