Finding overtime hours where employer is called to work immediately

[mikkom]

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?

 

[hilfy]

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

http://www.decisionfirst.com

 

[mikkom]

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?