Solved

Crystal Report - Calculate Time Clock punchtimes

Posted on 2009-07-05
7
490 Views
Last Modified: 2012-05-07
I am build a crystal report that shows an employee weekly time punches and calculate the employees hours.  So far, I am able to show the punches and clock actions but I am having a breakdown on calculating the time between clock in and clock out; thusly giving me the weekly sum of hours.

I must take into account any missed punches....  Any assistance is greatly appreciated.
SELECT     EmployeeNum_1.EmployeeID, EmployeeNum_1.TempId, EmployeeNum_1.Unldr_#, EmployeeNum_1.Last_name, EmployeeNum_1.First_name, 
                      EmployeeNum_1.Active, EmployeeNum_1.PayRateHourly, EmployeeNum_1.PayRatePercentage, EmployeeNum_1.SpecialPay, 
                      EmployeeNum_1.HolidayPay, EmployeeNum_1.SalaryPay, WorkDays_1.Workdate, WorkdayPunches_1.Punchkey AS PunchKey, 
                      WorkdayPunches_1.PunchTime, WorkdayPunches_1.Action, WorkdayPunches_1.Note, WorkdayPunches_1.Deleted AS PunchDelete, 
                      WorkDays_1.deleted AS WorkDayDelete, EmployeeNum_1.JobTitle
FROM         EmployeeNum AS EmployeeNum_1 INNER JOIN
                      WorkDays AS WorkDays_1 ON EmployeeNum_1.Unldr_# = WorkDays_1.Unldr_# INNER JOIN
                      WorkdayPunches AS WorkdayPunches_1 ON WorkDays_1.UID = WorkdayPunches_1.Punchkey
WHERE     (EmployeeNum_1.Active = 1) AND (DATEPART(wk, WorkdayPunches_1.PunchTime) = DATEPART(wk, @Date))
ORDER BY WorkDays_1.Workdate, WorkdayPunches_1.PunchTime

Open in new window

TimeCards.pdf
0
Comment
Question by:NCSO
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24780703
What does this mean?
"I must take into account any missed punches"
0
 

Author Comment

by:NCSO
ID: 24781683
if there is a clock-in fo that day but no clock-out, show a value of 0; this will alert the supervisor to address the missed time punch
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24783096
"calculating the time between clock in and clock out"

This would appear to be the logic you want but I don't know what any of the fields are .
numbervar tdiff;
If onfirstrecord then
tdiff:=0
else if {table.action} = "Clock in" then
tdiff:=0
//else must be clock out; see if previous record was a clock in record
else if {table.employeeid} = previous{table.employeeid} and previous{Table.action} = "clock in"
then
tdiff:= datadiff("s", previous(table.time},{table.time})/3600  //hours and decimal hours
else
tdiff:=0;
tdiff

0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:NCSO
ID: 24799121
Peter57r

Is the following correct?  If so, I am getting an error in the expression builder (Unrcognized Identifier)

=numbervar tdiff;
If onfirstrecord then
tdiff:=0
else if Fields!Action.Value = "Clock in" then
tdiff:=0
//else must be clock out; see if previous record was a clock in record
else if Fields!EmployeeID.Value = previous Fields!EmployeeID.Value and previous Fields!Action.Value = "clock in"
then
tdiff:= datadiff("s", previous Fields!PunchTime.Value,{Fields!PunchTime.Value})/3600  //hours and decimal hours
else
tdiff:=0;
tdiff
0
 

Author Comment

by:NCSO
ID: 24806253
Here is a screenshot
unreconized-identifier.JPG
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24806842
Remove the = from the first line.

What is that screen? I don't recognise it as part of any version of Crystal that I'm using.
0
 

Author Comment

by:NCSO
ID: 24807178
I am using the embedded version that is in Visual Studio 2005; I tried removing the "=" sign but I am trying to using the expression to fill a textbox.  When I use the code that you provided to me, the numbervar shows as an unrecognized identifer.

thanks
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question