Calculating Sick Time

[POrlowski]

I am working in Crystal Reports connecting to an SQL database.
When calculating sick time you take the total hours worked and divide it by 30 to get sick time. The issue is if the employee does not work a week then their total hours start all over again.
Group 1 is Employee
Group 2 is Payroll End date
Data looks like this
Dan
1/1/23 20
1/8/23 20
1/22/23 20
1/29/23 20

Ann
1/1/23 10
1/8/23 10
1/15/23 10
1/22/23 10
1/29/23 10

Betty
1/1/23 10
1/15/23 10
1/29/23 10

For Dan he has 2 dates in a row then he misses a week and would have to start his total hours again so his total hours would be 40.
Total hours is reset for the next employee.
Ann worked each week so she would have a total of 50 hours.
Betty would have a total of 10 hours because she only worked every other week. Her total would reset each time she missed a week.

Any suggestion how I can calculate total hours?

 

[pmax9999]

Hi POrlowski and welcome to Tek-Tips.

I got the required result by doing the following:

1. Create the following formula, place it in the Details section and suppress it:
[Code {@Hours}]
WHILEPRINTINGRECORDS;
Global NumberVar HRS;

If OnFirstRecord
Then HRS := {Table.Hours}
Else
If {Table.Employee} <> Previous({Table.Employee})
Then HRS := {Table.Hours}
Else
If {Table.EndDate} = Previous({Table.EndDate}) + 7
Then HRS := HRS + {Table.Hours}
Else HRS := {Table.Hours}
[/Code]

2. Create the following formula and place it in the Group Footer section of Group 2 (Employee):
[Code {@Hours_Display}]
WHILEPRINTINGRECORDS;
Global NumberVar HRS;
[/Code]

Hope this helps.

Cheers, Pete.

 

[POrlowski]

It did not work. I apologize If I was not clear, but you have your hours in the detail section my hours are in Group 2
I have attached a snip of what my set up looks like and how I changed the formula you cage me.
Your formula is only returning the hours they worked each week and never adding them up.

 

[pmax9999]

The report and data structure is still unclear to me, and I don't have time to try and dummy up some data so I can replicate your exact situation.

If you are happy to post a copy of the report file (suitably de-identified if preferred) I am happy to take another look.

Pete.

 

[pmax9999]

I've just had another look.

Your amendment to the {@Hours} formula looks to be correct. If you move that formula to GF2 and suppress it (it is only for the purpose of calculating the totals sought, not for display) and move the {@Hours Dislpay} formula provided (which you seem to have ignored completely) to GF1 I think you will get the result you are looking for.

If not, please post a copy of the report (with saved data) to help me understand the report structure.

Cheers
Pete

 

[POrlowski]

Attached is The report with saved data. I think that the one I have in green is working but I put yours in as well in grey. The report is run from 1/1/2023 through 7/2/2023. Payroll dates only show up if they have hours for that week. So I also had to check if the last date of the report was one of the weeks worked if not then back to 0.
I do have a bunch of extra stuff in there to check to see if my formulas were working like Total hours and Reg Date Diff.
Thanks for taking a look

 

[pmax9999]

Because my code checks both the employee name and end date fields with the previous record, it is only the first row on change of either group where this will work, therefore my formula needs to be in the GH rather than the GF.

So, move the {@CalulateTotalHoursForSick} formula from GF2 to GH2 and the results returned are in line with my understanding of what you were trying to achieve (and match the results for all staff from your formula, except for the last employee listed - my formula returns 40 hours where yours returns 0 hours - not sure what result you are expecting there).

It is of course entirely possible that I have misunderstood your requirements.

If this does not produce the result you believe is correct, please explain what the result should be for a specific employee, and the logic that supports that result.


Cheers, Pete.

 

[POrlowski]

Thanks Pete. When I move the formula I am getting the correct total hours worked. Thank you! When I look at my report I got 80 hours for Baker as well so I am not sure what happened there. Thank you for your help with this! Peggy