Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding overtime hours where employer is called to work immediately

Status
Not open for further replies.

mikkom

Technical User
Jul 23, 2010
29
FI
I have to find overtime hours where employee was called to work immediately. There are following field in database: datetime, employee, salary type. To the field salary type is marked overtime 50%, overtime 100% or alarm. I have to find overtime rows where same employee has marked alarm to same day. How can I do that?
 
I'm assuming you want all of the hours on a day where an employee has an "alarm". This can be a bit tricky. I assume that you have a separate Employee table with the employee name, etc. and an Work_Hours table that you described above. (Replace the table names with the names of your actual tables.)

You'll need to add a second copy of the Work_Hours table to the report. When you add a table that's already in the report, Crystal will throw a warning and ask if you really want to "alias" the table. Click on "yes" or "ok". Crystal will then add the table with "_1" on the end of the table name. In this case that will be Work_Hours_1.

Link the tables in the following order:

Employee --> Work_Hours on EmployeeID
Work Hours --> Work_Hours_1 on EmployeeID

Now, create a "SQL Expression" that will give you the date of the datetime from the Work_Hours table. SQL Expressions use syntax that is specific to the database - you CANNOT use Crystal functions in them! So, if you're using Oracle, this will be something like: Trunc("Work_Hours"."StartDate") and if you're using SQL Server, it might look like: CONVERT(date, "Work_Hours"."StartDate"). You'll need to find the syntax that is specific to your database. Do the same thing for the Work_Hours_1 table. I'll call these WH_Date and WH1_Date

Go to the Select Expert and, in addition to whatever filters you have in there, add something like this:

{Work_Hours.SalaryType} = 'alarm' and
{Work_Hours_1.SalaryType} <> 'alarm' and
{%WH_Date} = {%WH1_Date}

Now you'll use the data from Work_Hours_1 to calculate the overtime.

You'll probably have to tweak some of this for your specific data, but hopefully this will get you headed in the right direction with it.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
I got that working... But now I need to do that the other way around. I need find overtime hours but block off those overtimes hours where is alarm. How I can do that?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top