Solved

Crystal Report - Calculate Time Clock punchtimes

Posted on 2009-07-05
7
497 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

How to increase the row limit in Jasper Server.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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