SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Vacations, calendars, etc. etc. - where do I...

Vacations, calendars, etc. etc. - where do I start Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
nick-1121133
Posted Friday, October 23, 2009 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 17, 2009 5:07 AM
Points: 8, Visits: 39
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
Post #807679
emily-1119612
Posted Friday, October 23, 2009 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 19, 2010 3:29 PM
Points: 129, Visits: 595
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.
Post #808149
emily-1119612
Posted Friday, October 23, 2009 1:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 19, 2010 3:29 PM
Points: 129, Visits: 595
[quote]emily-1119612 (10/23/2009)
How is action time accrued?

I meant "How is vacation time accrued?
Post #808151
Jeff Moden
Posted Friday, October 23, 2009 8:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 20,175, Visits: 13,708
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

"Data isn't the only thing that's supposed to have Integrity."

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #808235
nick-1121133
Posted Friday, October 30, 2009 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 17, 2009 5:07 AM
Points: 8, Visits: 39
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
Post #811441
« Prev Topic | Next Topic »


Permissions Expand / Collapse

 
 
Copyright © 2002-2010 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use