| Cynthia |
|
|
Reply with quote | #1 |
I need to calculate the number of days between dates to get a consecutive number of days for an employee. It calculates the first group but does not continue to the next line. The formula is below. Can you tell how to fix this?
Shared NumberVar consecDays;
Shared NumberVar consecDaysMax;
if(Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) = {TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID} and
DateDiff("d",Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT}), {TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT}) <= 1 or
(DateDiff("d",Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT}), {TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT}) = 2 and
DayofWeek(DateAdd("d",-1,{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT})) in [1,7] and
DayofWeek(DateAdd("d",-2,{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT})) in [1,7])) then
consecDays:= consecDays + DateDiff("d",{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT},{TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT})+1
else
consecDays:= DateDiff("d",{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT},{TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT});
if(Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) <> {TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) then
consecDaysMax := consecDays
else if(consecDaysMax < consecDays) then
consecDaysMax := consecDays;
consecDays
The results look like: but I want
HRSTAKEN CONSCDAYS CONSCHRS HRS TAKEN CONSC DAYS HRS
40 6 48 40 6 48
8 6 56 8 7 56
etc.
Please help. Thank you.
|
| |
| Alan |
|
|
Reply with quote | #2 |
Debugging formulas are hard without data and the report. I can give you some advise on tracing the problem. Yhe issue looks like it is with the consecDays variable so the last 5 lines deal with that value. I would focus on these lines and walk through the formula with data for the values. |
| |
| Cynthia |
|
|
Reply with quote | #3 |
Thanks. I did solve the problem of the consecutive days by using the following line:
consecDays:= consecDays + ({TR_CC_HRIS_MANDATORY_ABSENCE.TR_HRS_TAKEN}/8).
The problem now is that it keeps the first day of absence at 0.00 when it should be 1.00. Do you know what in my formula can be changed so the day of vacation/holiday in the report shows this?
|
| |
| Cynthia |
|
|
Reply with quote | #4 |
I got the result in days and hours. However, I need to add a statement or statements that will recognize when the dates are no longer consecutive. Look at the example below.
3/11/2008 3/11/2008 8.00 9.00 72.00
5/23/2008 5/23/2008 8.00 10.00 80.00
After 3/11/2008 it should return to 1.00 b/c of the lapse between the two dates. Can someone help me with that?
|
| |
| Alan |
|
|
Reply with quote | #5 |
For this formula to return to 1.0, you will need to have this as a running total that will reset at a specific point, in your case date. Seeing as you are doing this in a formula this will require you to create a manual running total. This is basicly three formulas that control the running total process. I cannot get into the detail here as it is too long to write, but this is documented in the Crystal Reports help. Do a search on running totals and you should see an item for manual running totals. |
| |
| Cynthia |
|
|
Reply with quote | #6 |
Thanks for your suggestion Alan. I was able to unravel the problem, actually there were two. I reversed two of the statements below and added one statement to the end of the formula for holidays. The biggest problem was that I didn't write the original formula and it took me some time to understand the logic behind it. It does run correctly now. Thanks again.
DateDiff("d",Previous({TR_CC_HRIS_MANDATORY_ABSENCE.RETURN_DT}), {TR_CC_HRIS_MANDATORY_ABSENCE.BEGIN_DT}) <= 1 or
(DateDiff("d",Previous({TR_CC_HRIS_MANDATORY_ABSENCE.RETURN_DT}), {TR_CC_HRIS_MANDATORY_ABSENCE.BEGIN_DT}) >= 0 and
and added this statement to prevent non-consecutive holidays from adding together:
if(Previous({TR_CC_HRIS_MANDATORY_ABSENCE.EMPLID}) = {TR_CC_HRIS_MANDATORY_ABSENCE.EMPLID} and
{TR_CC_HRIS_MANDATORY_ABSENCE.TR_ABS_REASON}= "HLD") then
consecDays:= ({TR_CC_HRIS_MANDATORY_ABSENCE.TR_HRS_TAKEN}/8);
|
| |
