Hello, all.
Looking for some advice on processing attendance information.
I've been asked to report on employee attendance showing number of days and number of events.
There is an absence table which holds (amongst other items) absence reference (unique), employee number,
type of absence, start date and end date.
All well and good. However, the HR director would like all continuous absences of certain types to be treated as one event in each month.
i.e. Employee 123 has record SICK for 1/2/2010 to 2/2/2010 and record ABSENT for 3/2/2010 to 3/2/2010 should be treated as one event for February.
and Employee 456 with record SICK for 28/01/2010 to 2/2/2010 will be an event in January and in February.
There is also the situation that some administrators will enter absence daily for an employee, so that a week's absence is recorded as 5 separate entries.
Whilst I would like to believe that this will stop, it would seem prudent to expect it to continue.
I have a working day calendar with a date field and a simple 1 or 0 for working/non-working day which I currently join with the absence record to calculate days of absence.
My initial thought is to construct a temporary calendar table consisting of employee, date, absence type and a flag (tiny int),
which would be filled by joining the working day calendar (filtered on working days) and the absence table and setting the flag on absence days.
I could then parse this by month and employee for contiguous records of the required absence types with the flag field set to construct a simple event count table of
employee, date (year & month), event count, absence type.
Am I missing a better method? Should I consider self-joining on the absence record to 'grow' absence periods (which could then be 'chopped' into months)?
All thoughts welcome.
(I haven't posted trial data, but can do so - it's more guidance that I'm seeking).
Many thanks,
lex
soi là, soi carré
Looking for some advice on processing attendance information.
I've been asked to report on employee attendance showing number of days and number of events.
There is an absence table which holds (amongst other items) absence reference (unique), employee number,
type of absence, start date and end date.
All well and good. However, the HR director would like all continuous absences of certain types to be treated as one event in each month.
i.e. Employee 123 has record SICK for 1/2/2010 to 2/2/2010 and record ABSENT for 3/2/2010 to 3/2/2010 should be treated as one event for February.
and Employee 456 with record SICK for 28/01/2010 to 2/2/2010 will be an event in January and in February.
There is also the situation that some administrators will enter absence daily for an employee, so that a week's absence is recorded as 5 separate entries.
Whilst I would like to believe that this will stop, it would seem prudent to expect it to continue.
I have a working day calendar with a date field and a simple 1 or 0 for working/non-working day which I currently join with the absence record to calculate days of absence.
My initial thought is to construct a temporary calendar table consisting of employee, date, absence type and a flag (tiny int),
which would be filled by joining the working day calendar (filtered on working days) and the absence table and setting the flag on absence days.
I could then parse this by month and employee for contiguous records of the required absence types with the flag field set to construct a simple event count table of
employee, date (year & month), event count, absence type.
Am I missing a better method? Should I consider self-joining on the absence record to 'grow' absence periods (which could then be 'chopped' into months)?
All thoughts welcome.
(I haven't posted trial data, but can do so - it's more guidance that I'm seeking).
Many thanks,
lex
soi là, soi carré
