# 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
``````
TimeCards.pdf
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
What does this mean?
"I must take into account any missed punches"
0
Author Commented:
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
Commented:
"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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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 Commented:
Here is a screenshot
unreconized-identifier.JPG
0
Commented:
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 Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.