Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 General Design Question

Author  Topic 

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 11:03:36
Here is my problem. My company wants me to build a site that will allow them to track vacation accrual for them and track days off. I have an employee table, a request table at the moment. The employee table has accrual rate and a start date for when the accrual begins. My original thought was to take the accrual begin date and subtract it from the current date returning the figure in number of months. I would then multiply that by the accrual rate and subtract any requested days that had a status of taken.

They are telling me now that some employees vacation days expire and others don't. So, now I have to come up with a different way of doing this.

I have to have a way of showing expiration dates for the vacation.

I was thinking that I need to saved the accrued time on a monthly basis in a separate table and list an expiration date for the vacation that accrued that month.

I would appreciate any thoughts or suggestions for this solution I need. Kinda stumped here cause I am new to SQL and ASP.NET

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 16:25:20
Anyone???
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-21 : 16:40:03
Sounds like you're on the right track...
Request table (holds vacation requests; indicates whether they're approved/denied; and whether they're actually taken; etc...)
Employee table (holds empID/name; accrual rate; date accrual begins; expiration date; I'd consider a computed column to hold the actual vacation accumulated).

But it depends on your business rules...vacation accrues on the first day of the month for the previous month? Last day of the month? What's the smallest measure of vacation 1 day? 8 hours? 4 hours? Does vacation expire per employee or can an employee have time that expires and time that doesn't (need vacation type table?) I guess if all you're doing is tracking you don't need to worry about employees overdrawing their vacation?
Go to Top of Page

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 16:56:55
It accrues on the first day of the month for the prior month and they accrue a half day at a time.

Could you explain the computer column thing you mentioned? Are you thinking a column in the employee table that will the total accrued?

Thank you so much for responding. Again, I am new to all of this so just being able to talk through it with someone with some experience is greatly appreciated!!!
Go to Top of Page

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 17:00:32
Well, I say accrue a half day at a time, but that isn't right. They can use them in half day increments, but they accrue at a specific rate based on the employee.
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-09-21 : 17:30:27
Exactly, a computed column basically contains a formula that recalculates everytime you reference the field.

--typo
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-21 : 22:48:23
Do you need 2 types of days off - one that doesn't expire (as you originally had it) and one that does. You can then accrue at one end and de-accrue at the other (if you see what I mean).

In fact what you probably want is to cover all variations, so you'd have a table "vacation_type" containing the expiry period (e.g. 6 months or whatever) and maybe some other information such as a description. Each employee would then have a row in an employee_to_vacation_type containing their particular start date for this vacation entitlement. From here you can work out how many days the employee has accrued between the time now and (now - expiry_period). Do this across all vacation types (less any vacation taken). Your non-expiry case can just have a very long period. Nice & easy and you can have as many as you need for any oddball or one-off cases. You can also make it smart by allocating vacation requests from the vacation_type with the shortest expiry period first.
Go to Top of Page

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 09:49:58
Yes, there will be some that expire and some that don't expire. There also may be individual exceptions. Is there a way to make it automatically calculate and update the computed column automatically say on the 1st of each month at 12:01am?
Go to Top of Page

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-22 : 11:41:11
Let me ask you this. The computed column thing is really kewl. I hadn't gotten to that section of the book, but it looks like that is really going to help. Is there a way to update a column in the user table with say the requested days off that they have taken from another table automatically with SQL instead of doing the math in the query in the page.

You guys have been an incredible help already and as a newb, I can't tell you how much I appreciate it.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-24 : 19:34:15
You do not need to do this (although you can run a scheduled job to do some work but that's a different topic).
If you store your data correctly (i.e. as I recommended ;) ) you can work all this out at the time you select it. There should be no need to run periodic updates across your data. You can already tell how long a particular vacation is valid for, when it was granted and what day it is today. All you need is there, even to do the special cases.
Go to Top of Page
   

- Advertisement -