October 26, 2020 at 9:39 am
Hi all
I need your advise for the below case:
I’ve a leave request system that users request leaves through it and it differentiate between working days, weekends & holidays means it counts only the working days
I’ve already two tables one for employees data like (name number title dept.) and the other table for leave data like (startdate enddate numberofdays leavetype)
Now, I need to add leave entitlement option to calculate if user can take leave or not (will be paid or unpaid) and his current balance
We have two options for leave: (30 days each 365) and (21 days each 365) and that days shouldn’t be added to balance one time
means when user need to take a leave and he works for (ex. one month) his entitlement should be 30/12=2.5 days plus his old balance (if he have)
my question is – should I create another table or add that data to employees table?
And how to calculate entitlement days based on his leave history
Sorry for the prolongation, but I wanted to clarification my case
October 26, 2020 at 11:27 am
Since it's a calculation based on the current state of the data couldn't you just use a query? Why does the balance have to be stored? Perhaps it could be a persisted calculated column. Is there a data "purge process" that removes older data from the database?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 26, 2020 at 11:44 am
Since it's a calculation based on the current state of the data couldn't you just use a query? Why does the balance have to be stored? Perhaps it could be a persisted calculated column. Is there a data "purge process" that removes older data from the database?
no "purge process" but actually the old data are not available as the system is almost new
so I've to start with cut off date by entering the current balance for all then will calculate coming entitlement leaves and balance based on it
Am I true? just thinking
October 26, 2020 at 11:56 am
Steve Collins wrote:Since it's a calculation based on the current state of the data couldn't you just use a query? Why does the balance have to be stored? Perhaps it could be a persisted calculated column. Is there a data "purge process" that removes older data from the database?
no "purge process" but actually the old data are not available as the system is almost new
so I've to start with cut off date by entering the current balance for all then will calculate coming entitlement leaves and balance based on it
Am I true? just thinking
Is the "current balance for all" stored in either of the 2 tables: employees, employee leave?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 26, 2020 at 12:20 pm
tanehome wrote:Steve Collins wrote:Since it's a calculation based on the current state of the data couldn't you just use a query? Why does the balance have to be stored? Perhaps it could be a persisted calculated column. Is there a data "purge process" that removes older data from the database?
no "purge process" but actually the old data are not available as the system is almost new
so I've to start with cut off date by entering the current balance for all then will calculate coming entitlement leaves and balance based on it
Am I true? just thinking
Is the "current balance for all" stored in either of the 2 tables: employees, employee leave?
yes, will be...this is what I'm asking about ..should I store them with any of exist table or keep them with the new table?
October 26, 2020 at 12:31 pm
Steve Collins wrote:tanehome wrote:Steve Collins wrote:Since it's a calculation based on the current state of the data couldn't you just use a query? Why does the balance have to be stored? Perhaps it could be a persisted calculated column. Is there a data "purge process" that removes older data from the database?
no "purge process" but actually the old data are not available as the system is almost new
so I've to start with cut off date by entering the current balance for all then will calculate coming entitlement leaves and balance based on it
Am I true? just thinking
Is the "current balance for all" stored in either of the 2 tables: employees, employee leave?
yes, will be...this is what I'm asking about ..should I store them with any of exist table or keep them with the new table?
Ok it wasn't clear to me this is about the carryover balances. Good question btw. In my opinion, because it's a 1 time discontinuity, I would create a new table to store the carryover balances. I'd set it up as if there's a possibility of future 1 time discontinuities tho. I'm interested to hear other opinions as well 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 26, 2020 at 12:40 pm
Actually I have to waffle and say maybe it belongs in the employees table
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 26, 2020 at 1:21 pm
The employee's current leave status (yes/no, days/year) can go in the main employee table. It should be encoded, such as a tinyint. If the leave status changes, you store the leave status history in a separate table. You never want to lose any employee info because of possible lawsuits, etc..
I suggest you accumulate leave balances periodically. How long between each accum is up to you (by pay period is typical). But you (a) don't want to calc the entire amount every time the employee may want to check on it; (b) need a method to allow special/olne-time allocations/deallocations of leave (bonus/make up leave given, leave surrendered/lost, etc.). It's a good idea to store the allocations in a separate table, with the current total in the main employee table. You can purge the separate leave allocations on whatever schedule works for you.
Be aware that laws may require you to keep a certain amount of employee info for a given period of time. Verify that before you delete any employee-related info.
Btw, the calc for leave after 30 days is 30*(30/365.0), not "30/12"; over time 30/12 slightly overstates the leave amount, and, worse, is inconsistent with the rules stated (30 days per 365 on work status). Notice, too, the new calc works more consistently for the other option: 30*(21/365.0), whereas a shortcut method does not.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 26, 2020 at 1:46 pm
You also may want to be sure you completely understand ALL the rules for leave taking, including such possibilities as minimum time between leave-takings. As a calendar year's length is not always 365, due to leap years, you also may want to give thought to the fact that every 11 to 13 years, a 3rd bi-weekly paycheck occurs in 3 months out of the year instead of just 2, leading to a 27th pay check occurring in a calendar year, instead of the usual 26, so the vagaries of the Julian calendar are hard to avoid whenever you need some kind of regular calendar-based time tracking system. I'm fairly certain having a table with at least the next 30 years of daily dates in it, along with a method to further extend that table, plus columns to indicate that a given day is a workday, weekend, or holiday; could be quite useful here.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 26, 2020 at 1:50 pm
the main problem that I face now is the employees could be entitle for 21 days each 365 for a certain time then he will be treated by 30 days each 365....I'm trying to find a way to manage this issue
October 26, 2020 at 2:09 pm
Again, as I noted above, the current leave status (30/365 in this case) should be stored in the main employee "header"/"master" table. Prior leave status(es) (21/365 in this case) would be put in the history table. You could store days per 365, but I still personally prefer a code / encoded value (such as 0=no leave; 1=21/365; 2=30/365; 3=...).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply