Sickness - no of occurences

[acessn]

Using CR9.0, Microsoft SQL server, reporting through WFC5.0

I need to create a report showing statistics on absence due to sickness. We have different pay codes for sickness, i.e. "sick 1-3 days", "sick 4-16 days" etc. It's rather easy to find number of hours and the days for each pay code, but I also need to get number of occurences. In the database, the abscence is stored in seconds per day. If you have been sick for five days, five times, this means 5 occurences of "sick 4-16 days"

Example: Mr.Mister has been sick for a total of 29 days the past year. How many occurences is there of "sick 1-3 days", "sick 4-16 days" etc.

Any ideas?

Best regards, Bjorn

 

[MrBillSC]

If you use different pay codes for each of the blocks of sick days, couldn't you just count the occurances of each sick code?

MrBill

 

[lbass]

If this is one field, with results varying by number of days sick, then you could create formulas like:

//{@sick1-3}:
if {table.code} = "Sick 1-3 days" then 1

//{@sick4-16}:
if {table.code} = "Sick 4-16 days" then 1

...etc. Then you could right click on each formula and insert a summary (SUM, not count) at the group and/or report levels.

-LB

 

[acessn]

Thanks for the response, but these suggestions won't work. They'll give number of days for each pay code, not no of occurences.

If you have been sick one weeek, you'll end up having 7 days of absence(This number you'll get with count), but this is just one occurence of "sick 4-16 days".
You could also be sick for 16 days in a row, and these 16 days will be another occurence of "sick 4-16 days".

With this scenario you'll have 23 days of absence and 2 occurences of sick 4-16 days.

It seems like I have to create a counter of some sort, but I'm not quite sure howto.

The report will typically be run to find numbers for last year.

Regards, Bjorn

 

[GJParker]

please give a sample of your input data. I have resolved this situation in the past and will try to find the report.

Gary



Gary Parker
MIS Data Analyst
Manchester, England

 

[acessn]

Thanks Gary.

I'll try to illustrate my input. I'm reporting from a view, and will be interested in the following fields:

Personname, personnr, paycode, timeinseconds, applydate
Mr.X 1005 sick 1-4 14400 01.01.2005
Mr.X 1005 sick 1-4 9000 01.01.2005
Mr.X 1005 sick 1-4 16800 02.01.2005
Mr.X 1005 sick 1-4 9000 02.01.2005

So, as you see, you can also have several occurences of "sick 1-4" during one day aswell. This is because you withdraw lunchbreak automatically after 4 hours, and then start over again, earning seconds in a new record.

If I'm doing a running total, I could evaluate on change of the date field to get the number of occurences on different dates, but I'll also have to check whether the same paycode is present on the next day...

Regards, Bjorn

 

[GJParker]

You will need to have the report grouped by personnnr and create a formula to accumulate the occurences

place this in the details section

whileprintingrecords;

shared numbervar Occurrence;

if not({MyTable.Paycode} Like 'Sick%') then
    if onfirstrecord then
       Occurrence:=0
    else
        if {MyTable.Personnr} <> previous(MyTable.Personnr) and 
           {MyTable.ApplyDate} <> previous({MyTable.ApplyDate}) then
            Occurrence:=0
        else
            Occurrence:=Occurrence
else
    if Occurrence=0 then
        Occurrence:=1
    else
    if onfirstrecord then
        Occurrence:=1
    else
        if {MyTable.personnr} <> previous({MyTable.personnr})and 
           {MyTable.ApplyDate} <> previous({MyTable.ApplyDate}) then
            Occurrence:=1
        else
            if DayOfWeek ({MyTable.ApplyDate},crsunday ) > 2 then 
                (if  {MyTable.ApplyDate} - previous({MyTable.ApplyDate}) <> 1 then
                    Occurrence:=occurrence +1)
            else
                if {MyTable.ApplyDate} - previous({MyTable.ApplyDate}) <> 3 then
                    occurrence:=occurrence+1;
    

Occurrence

and display the value in the group footer using this formula

@TotalOccurrence
shared numbervar Occurrence;

This won't give you exactly what you want but you should be able to modify it to count each different sick type.

Hope this helps

Gary Parker
MIS Data Analyst
Manchester, England

 

[acessn]

Looks like we're on the right track here. I'm gonna start working on this report during next week, so we'll see.

Thanks a lot!

Regards, Bjorn