October 23, 2009 at 2:37 am
Here's the beginning of the problem and any feedback will be very welcome - I am not seeking a programmed solution just yet (although if someone has one of course I am not going to reject it) - just any feedback from people who may have faced a similar requirement OR who can see the issues involved.
I need to record employee holidays.
I also need to record how much vacation an employee has available ( i have base data).
I need to use each 'vacation event' to update how much vacation an employee has left.
I need to identify the TOTAL absence and TOTAL WORKDAYS absence for each 'Vacation Event'
We operate on a Middle East Calendar (work week is Sunday through Thursday) and the Government frequently announces ad hoc public holidays - which our staff are entitled to.
Staff have un-used public holidays AND vacation days from previous years.
Q1. Should I create a calendar table that I use in all situations? If so:
What would be best?
Can anyone recommend a script?
How do I make it so that if public holidays are added I can update the public holidays for all staff?
Q2. Can anyone suggest a table structure for me to start playing with
What I really need (grovelling!) is some help/guidance/advice on HOW to proceed with this.
Nick, Dubai
October 23, 2009 at 1:48 pm
How is action time accrued? Is it based on employment start date, years working at the firm or other factors.
Generally I would think about it like this. An employees' available vacation time equals:
(the initial value carried over)+( the amount accrued over time)-(the amount used)
You could do it with one table that would have three types of records.
-initial carryover amount
-accruals added depending on your business logic
-decrements, the use of vacation time
>>I need to use each 'vacation event' to update how much vacation an employee has left.
I wouldn't update a field that stores the available time. Calculate it like above.
I would think a calendar table would come into play here.
October 23, 2009 at 1:50 pm
emily-1119612 (10/23/2009)
I meant "How is vacation time accrued?
October 23, 2009 at 8:01 pm
nick-1121133 (10/23/2009)
Here's the beginning of the problem and any feedback will be very welcome - I am not seeking a programmed solution just yet (although if someone has one of course I am not going to reject it) - just any feedback from people who may have faced a similar requirement OR who can see the issues involved.I need to record employee holidays.
I also need to record how much vacation an employee has available ( i have base data).
I need to use each 'vacation event' to update how much vacation an employee has left.
I need to identify the TOTAL absence and TOTAL WORKDAYS absence for each 'Vacation Event'
We operate on a Middle East Calendar (work week is Sunday through Thursday) and the Government frequently announces ad hoc public holidays - which our staff are entitled to.
Staff have un-used public holidays AND vacation days from previous years.
Q1. Should I create a calendar table that I use in all situations? If so:
What would be best?
Can anyone recommend a script?
How do I make it so that if public holidays are added I can update the public holidays for all staff?
Q2. Can anyone suggest a table structure for me to start playing with
What I really need (grovelling!) is some help/guidance/advice on HOW to proceed with this.
Nick, Dubai
You've taken Step 1 already... you've identified what you think you need to do for tasks. Now you just need to solve them. A process chart (like a flow chart but not record based) would probably help keep the big picture from being too complicated and will also help you NOT miss stuff.
As for the Calendar table? There are a lot of good ones although many are very much overcomplicated and some miss critical columns like "if today is Thursday, which Thursday of the month is it?" I would only add enough to a Calendar table to solve the current problem(s)... it can always be expanded later.
I'd also recommend that you keep a separate Holiday table. Sure, you can use it to update the IsHoliday column of the Calendar table but a separate Holiday table can be used to identify a whole lot more about a Holiday without having a bazillion nulls in holiday related columns in a Calendar table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2009 at 4:17 am
Many thanks Jeff and Emily - the input is hugely appreciated as nobody else in the company has any idea what I am talking about as I pace up and down in the courtyard, swearing to myself about being unable to do something or other in SQL. 🙂
Also, apologies for the delay in acknowledging your input - I have been working on several non-SQL projects and I simply have not made time to come back into the forum. Fridays and Saturdays are our weekend here in Dubai, so I am relaxing and catching up!
Emily, vacation accrual is 30 days per year, available from 1 jan each year. New joiners have a pro-rated amount of leave avialable when they join, i.e. 2.5 days per month for each month remaining in the calendar year - I'd like to automate this (I'm sure I adding it to my insert trigger which fires when I add a new employee seems the most logical and simple method to me).
I've realised that our application has a Business Calendar component (SQL based and located inside the application DBs) which I can configure to reflect work/non-work days, so that part is not a problem. It does not appear to allow me to define Public Holidays, however that's not really a problem - these can be globally assigned in my leave table (every employee has so many public holidays available) and I am thinking that a leave event is comprised of a combination of:
~ public holidays
~ paid vacation days
~ unpaid leave days
and my application will allow a runtime calculation of total days absent and total work days absent, which I will then 'deduct' from the running total of available days.
Bearing all your advice in mind, I'll attempt to construct some tables and post them here with the associated queries and - if you have time/interest - you can take a look and offer some (constructive???) criticism!
Many thanks again.
Nick
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply