Hi
I have tried searching but to no avail. I needto be able to identify consecutive dates in order to group sickness periods together. I then need to count them so that if the total is <28 it counts as short term sick and if >28 as long term sick.
Records will be in an SQL table Timesheet, and I can identify employee, date and sick leave recorded in the activity field. I had thought of appending a copy of these records to another table called Sickness.
Sickness would have EmployeeID, Date and a field SicknessType (where 3 = unallocated, 2 = long term and 1= short term, with default being 3)
It's easy as far as creating the Sickness table but how do I then identify consecutive dates, and count them so I can allocate a 1 or 2 sicknesstype code.
Further, while a record =1 it could become 2 if sickness continues but once 2 it will not revert.
An added wee complication is that if sick Monday to Friday for 2 conscutive weeks this would count as 10 days sick, not 12 since the two scheduled days off don't count. This is made even more complex by there not really being a way to identify what are scheduled days off (I didn't do that bit of the db!)
I am quite happy to add additional fields if necessary and will try to accommodate the days off bit if someone can give me an idea on the consecutive stuff.
Any help would be most appreciated.
Thanks in advance!
I have tried searching but to no avail. I needto be able to identify consecutive dates in order to group sickness periods together. I then need to count them so that if the total is <28 it counts as short term sick and if >28 as long term sick.
Records will be in an SQL table Timesheet, and I can identify employee, date and sick leave recorded in the activity field. I had thought of appending a copy of these records to another table called Sickness.
Sickness would have EmployeeID, Date and a field SicknessType (where 3 = unallocated, 2 = long term and 1= short term, with default being 3)
It's easy as far as creating the Sickness table but how do I then identify consecutive dates, and count them so I can allocate a 1 or 2 sicknesstype code.
Further, while a record =1 it could become 2 if sickness continues but once 2 it will not revert.
An added wee complication is that if sick Monday to Friday for 2 conscutive weeks this would count as 10 days sick, not 12 since the two scheduled days off don't count. This is made even more complex by there not really being a way to identify what are scheduled days off (I didn't do that bit of the db!)
I am quite happy to add additional fields if necessary and will try to accommodate the days off bit if someone can give me an idea on the consecutive stuff.
Any help would be most appreciated.
Thanks in advance!
