Just to let people know that I won't be monitoring or responding to posts here during that timeframe.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
The company I work for has this awful database for logging vacation.
We've talked about recreating it and I am having a bit of a difficult time dealing with another developer. Basically the application allows our employees to log vacation time.
There are some rules however:
Benefit
Non Exempt Status
•Less than 5 years – 2 weeks or 80 hours-per-year (3.08 hours-biweekly),
•5 – 10 years – 3 weeks or 120 hours-per-year (4.62 hours-biweekly),
•More than 10 years – 4 weeks or 160 hours-per-year (6.16 hours-biweekly).
Exempt Status
•Less than 5 years – 3 weeks or 120 hours-per-year (4.62 hours-biweekly),
•5-10 years – 4 weeks or 160 hours-per-year (6.16 hours-biweekly),
•More than 10 years – 5 weeks or 200 hours-per year (7.70 hours-biweekly).
Basically an employee is either exempt or non-exempt and based on those rules you get a certain amount of accural. One of the developers here wants to store running totals and amount of total annual benefit hours right inside the employees table. I keep explaining that this sort of thing is wrong and referred him to some normalization tips.
My thinking was a master employee table with just employee information and a field as to whether the employee is Exempt or Non-Exempt.
Then the rules that I pasted above would be in their own seperate table linking the employee with the status as well as doing a EmployeesTable.HireDate BETWEEN TodaysDate - EmployeesTable.HireDate to find out which rule the employee would follow (<5 years, 5-10 years, >10 years). Hopefully that BETWEEN wont bite my thinking :|, as I've never had to deal with such an app.
In any event in addition to this I thought that the actual transactions (accruing vacation, taking vacation, selling vacation off) all of these transactions would go into one table. This way you may have + / - values and the SUM of these values would give you your available vacation hours. Basically each line item will have an identifier as to what "Type" the line item is (vacation day, accrual, accural benefit, sell vacation, etc).
My counterpart thinks otherwise and to have a table of just accurual vacation hours, a table of used vacation hours, etc.
The thing that may be a bit more difficult for me is the part where an employee gets accrued bi-weekly hours. For instance, if the employee is non-exempt and he / she has been here <5 years then they accrue 3.08 hours bi-weekly. Plus the maximum number of vacation hours that can be accrued is double the eligible annual benefit. So in this case:
Non Exempt Employees
•Less than 5 years – 4 weeks or 160 hours maximum accrual,
•5 – 10 years – 6 weeks or 240 hours maximum accrual,
•More than 10 years – 8 weeks or 320 hours maximum accrual.
Exempt Employees
•Less than 5 years – 6 weeks or 240 hours maximum accrual,
•5-10 years – 8 weeks or 320 hours maximum accrual,
•More than 10 years – 10 weeks or 400 hours maximum accrual.
Im wondering if I should just setup 2 jobs one to handle the biweekly stuff and the other to check maximum accruals?
How does this sound, or what may / may not bite me?
Anyone develop something similiar that can give me some pointers...surely Jeff's around :).
Thanks,
Jon
Programmers HowTo's -- http://jhermiz.googlepages.com
I have a column YearGiven (smallint, null). I need to be able to use a WHERE clause that will include all years. Something like this:
SELECT * FROM Documents WHERE YearGiven = *
I realize if I eliminate the WHERE clause entirely this will produce the desired result. However in my VB app my Select statement will have to take into account that the YearGivenTextBox used in the Full Text Search may be empty. This is not a problem if I can assign a value to the empy textbox variable that will include all years.
Not data corruption, but there's a guy in another forum trying to mount a CLUSTERED INDEX on a 140,000,000 row table and it ran for more than 23 hours before some bright spark pulled the plug.
If you guys can help with whether it is likely to need to take that long, and if not what might be amiss, he can be found:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55805
Cheers,
Kristen
Back last October Paul DB mentioned the MS Log Parser tool and that he was using it for Exchange Transaction Logs.
I'm not an SQL person myself and would dearly love a little help to get started. Paul, I would be most grateful if you could post/send me even one example of the command line you use to get info out of Exchange T Log files. (I'm using 5.5 at present.)
Much thanks in advance,
Babylon Burns.
I'm in China doing TechEds in Shanghai, Guangzhou, Beijing and Hong Kong - returning to the office Monday 2nd October.
There may be significant delays in my replies to any topics on this forum. Just fyi so you don't sit waiting for me to respond.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
It's been pretty quiet here recently - hopefully people either aren't experiencing corruptions or they're finding what they need in the previous threads.
Just a quick note to say that I'll be totally offline from tomorrow until January - going diving in Indonesia (http://www.wakatobi.com).
Happy holidays and may your pagers be silent throughout
Cheers
Paul Randal
Principal Lead Program Manager, Microsoft SQL Server Core Storage Engine (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
http://blogs.msdn.com/sqlserverstorageengine/default.aspx