Calculating infomation in different rows

cuzintone · October 28th, 2004, 08:55 AM

I am building a time clock app for my company (why not buy one, I know, I know) anyways, typical design, person walks up, presses in after putting in their ID/password combo. The time/date and their ID is recorded in a punchClock database. Same scenario happens again when they punch out...i.e., ID/password combo, then press the out button.

At the end of the week, I have to calculate the number of hours worked by each employee...each employee is a separate row in the punchclock table, so how would I go about pulling this information to print a time report for each employee that shows the number of hours worked for a given week.

So in effect, the layout of the data is this in the punchclock table:

ID EMPID PUNCHDATE PUNCHTIME
1 1 10-25-2004 00:00:00 10-25-2004 08:00:00
2 3 10-25-2004 00:00:00 10-25-2004 08:15:00
3 2 10-25-2004 00:00:00 10-25-2004 09:00:00
4 1 10-25-2004 00:00:00 10-25-2004 15:30:00
<so forth>

So basically what I'd do is gather this information for w/e 10-30-2004 (which is this week) and produce the following

empid/date 10-25-2004 10-26-2004 ... TOTAL
1 8:00am
                3:30pm
                ------
                  7.5.... 37.5

I am a newbie at the whole database app programming thing. Any help would be greatly appreciated.

-T

happygv · October 29th, 2004, 10:58 PM

First of all I would sugesst you to remove the PunchDate Column from there, as the same is very much available with PunchTime column. Also to add another column that records IN/OUT. It would be better if those two are recorded in the same row for a related out/in activity.Here comes a question, does the employee never go out inbetween and come in at sometime later? Does that too go into the database? If so you will have consider that too.

In that case my table structure would be,
EmpId, TimeIn, TimeOut
So that once an employee comes in, the time is recorded, and the next OUT is recorded to the same record, next time if he comes in (be it same day or different day) that is recorded on a different record, thus each record, shows one complete event(in and out) that shows how long the employee was in the office.

Use DateDiff function to find the time in number of hours worked. Check Online documentation for help on that. No mention about what database you use. IS there any frontend UI involved in this, then better do the formatting and other stuff from the frontend. Just use backend to pull out the data required. Try out that and post here if you are stuck up somewhere in the middle.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

happygv · October 29th, 2004, 11:37 PM

Here is a sample of that.

Code:

Create Table Employee
(
EmpId int,
EmpName varchar(10)
)
go

create table hoursworked
(
EmpId int,
TimeIn datetime,
TimeOut datetime
)
go

Insert Employee values (1,'First')
Insert Employee values (2,'Another')
go

Insert HoursWorked values (1,'2004-10-30 00:00:00.000','2004-10-30 06:00:00.000')
Insert HoursWorked values (1,'2004-10-30 06:15:00.000','2004-10-30 08:30:00.000')
Insert HoursWorked values (2,'2004-10-30 00:00:00.000','2004-10-30 08:30:00.000')
go

Select * from (Select e.EmpId, e.EmpName, Convert(varchar(10),TimeIn,121) DayOfRep, 
(Cast(DateDiff(second,TimeIn, Timeout)/60.0/60.0 as decimal(6,2))) hrswkd
From Employee e, HoursWorked h
Where e.EmpId = h.EmpId) TempTable
Group by EmpId, EmpName, DayofRep, hrswkd
Order by EmpId, sum(hrswkd)
compute sum(hrsWkd) by EmpId
go

This is how it would show up.
EmpId EmpName DayOfRep hrswkd
----------- ---------- ---------- --------
1 First 2004-10-30 2.25
1 First 2004-10-30 6.00

                                                 sum
                                                 ======================================
                                                 8.25

2 Another 2004-10-30 8.50

                                                 sum
                                                 ======================================
                                                 8.50

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

cuzintone · November 1st, 2004, 03:42 PM

First...thanks...I grew a gray hair tryin to figure it out...big long one on top of my head...

The design just changed...got to love management...now the employees walk up to a computer enter their ID (badge #) and press IN or OUT (possibly a SUBMIT then I'll scan the punch for their ID on that day)...hopefully I can make the query that you wrote work in that situation.

-T

cuzintone · November 2nd, 2004, 10:52 AM

Since there is a front end (web based) how do I make these updates. I'm thinking I should use temporary tables and stored procedures. I made the change to the punch clock table, so it is structured as follows:

PunchID int (PK)
EmployeeID int
PunchInTime datetime
PunchOutTime datetime

I figure it has to take a lot of code to figure these punches because now my punch clock looks like this

PunchID EmployeeID PunchInTime PunchOutTime
-----------------------------------------------------
    1 3 11/1 8:00 NULL
    2 1 11/1 8:15 NULL
    <so forth>

now employee 3 is back to punch at 4:30pm (16:30)...

-T

happygv · November 2nd, 2004, 05:48 PM

Hi there,

You got to order that by PunchInTime DESCENDING if you wanted to use SELECT and then update that, and compare against the EmployeeId. Also additionally if required check if PunchOutTime is NULL, if that was set to allow NULL by default.

Code:

Update TABLENAME set PunchOutTime = getdate() 
Where EmployeeId = 3 and PunchOutTime is NULL

So always ensure that any employee who comes in should go out to make that event(in this case that row) complete. So always only one row for any employee would have PunchOutTime as NULL. When the employee comes in the next time a new record is inserted, with punchouttime as NULL again, be it the same day or different day.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

cuzintone · November 22nd, 2004, 05:36 PM

What I finally ended up doin (much later huh?) was when I insert the punch, I mark the employee in in the Employee table and store the record number of the inserted punch. When the employee wants to punch out, I simply change their 'IN' to an 'OUT' retrieve the punch ID, then update the record to reflect their out time.

neal_rainman · March 6th, 2005, 10:28 PM

hi i am very new to sql database and trying to develop time clock application and ended up finding some here....thanx all of you guys those who help us new bees to learn new things. i tryed some but i am not able to go any further like how do i create the stored procedure which will update all data in a new table, i have some idea about sotred procedure but struggling how to put together the coding. my front end (Web based)and also need some help if someone reading some text from some web page how long tht person read tht tracking tht. not by letting user inputting the time but like make them sign in and sign out, any suggestion will be greatly apreciated.

tahnx once again