Solved

Crystal Report - Calculate Time Clock punchtimes

Posted on 2009-07-05
7
481 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
Comment Utility
What does this mean?
"I must take into account any missed punches"
0
 

Author Comment

by:NCSO
Comment Utility
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
Comment Utility
"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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:NCSO
Comment Utility
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
Comment Utility
Here is a screenshot
unreconized-identifier.JPG
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
How to increase the row limit in Jasper Server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now