Solved

Crystal Report - Calculate Time Clock punchtimes

Posted on 2009-07-05
7
500 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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pull date to Group header 6 36
Date is within the current week in Crystal Reports 2 119
Crystal Reports Licensing Questions 4 86
Analyze Shared Variable? 7 44
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

739 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