Date calculation issue...multiple possibilities

[agray123]

Hi all,

I am working on a report to calculate employee benefit payments that were not forwarded on to our third party administrator. We need to generate payment for employer shares of benefits while employees were on leave without pay.

The data I have thus far looks like this:
120449
11/7 LOA
12/1 RET
126551
4/22 LOA
5/20 RFL
9/5/ LOA
10/2 RET
126723
3/31 LOA
6/9/ RFL
128121
3/3/ LOA
6/2/ RFL
128343
3/17 LOA
3/31 RFL
128547
3/1/ LOA
11/5 RET
133933
6/5/ LOA
6/16 RFL
6/23 LOA
8/18 RFL

I need to be able to generate the time between LOA and RFL OR between LOA and RET or TER without catching the time between RFL and TER/RET...

This will give me the time period someone was out so I can begin calculations on how much money we owe!

 

[synapsevampire]

Time meaning the number of days?

And does this:

"I need to be able to generate the time between LOA and RFL OR between LOA and RET or TER without catching the time between RFL and TER/RET"

mean that you want the timebetween LOA and RFL OR between LOA and RET or TER excluding the time between RFL and TER/RET? Or that you want the time only for those 2 fields?

Expected results generally help to clarify things better than text.

Whatever it is that you need, consider using variables to store the start and end times.

I assume that you're grouped by the number (employee?), and that you have only one row of each type (LOA, RET, etc...).

So in the details place a formula to trap the date for use in the group footer:

//Group header @nabdates
whileprintingrecords;
datevar LOA := cdate(1950,1,1)
datevar RFL := cdate(1950,1,1)
datevar RET := cdate(1950,1,1)
numbervar datesdiff := 0

//Details @nabdates
whileprintingrecords;
datevar LOA;
datevar RFL;
datevar RET;
numbervar datesdiff;
If {table.type} = "LOA" then
LOA := cdate({table.Date});
If {table.type} in ["RFL","RET"] then
LOA - {table.date}

This would now display the difference in time for each change, if you need summaries, add them up in the datesdiff variable and display them in the group footer.

BTW, you reference TER yet there's no examples of it.

-k

 

[agray123]

Correct, I do need days, but I can have more than one period of leave without pay...

LOA 9-17-01
RFL 10-21-01
LOA 12-12-01
RET 01-09-02

These leaves may end with a RET (retirement) or TER (termination) but I could also have a return from leave followed later with a TER/ RET. I do not want these days though, only the time the employee was not being paid and we were not sending our share of the money...I will try the variables and see how that goes.

The odd thing is, our third party administrator never knew this...I should just ask them for a bill off of the interfaces we send them!

 

[agray123]

Expected results:

LOA 9-17-01
RFL 10-21-01
LOA 12-12-01
RET 01-09-02

149 (days...made up ###)

I also am running into a remianing text not part of formula error at this point:
datevar LOA := cdate(1950,1,1)