asked on

Crystal Report - Calculate Time Clock punchtimes

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

peter57r

What does this mean?
"I must take into account any missed punches"

NCSO

ASKER

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

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NCSO

ASKER

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

NCSO

ASKER

Here is a screenshot
unreconized-identifier.JPG

peter57r

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.

NCSO

ASKER

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