Employee Vacation days
Page 1 of 1
Employee Vacation days
Hi
I am developing HR data warehouse where one of the requirments is to calculate the number of days an employee is on vacation/leave for each month. From the source system I get emplyee ID , Vacation start date and vacation end date. if vacation period span over only one month I can calculate the date difference but in case vacation starts in 1 month and end in next month or even the period is over more than 2 months how can I model such a fact table so I report correct number of days for each month.
Example
For employee ID A234 the vacation start date is 21st September 2013 and end date is 7th October 2013 I need to report as follows:
EMPID MONTH NrofDays
A234 9 10
A234 10 7
Similarly for employee A235 the vacation start date is 25th August 2013 and end date is 5th OCtober 2013 so I need to report it as:
EMPID MONTH NrofDays
A235 8 7
A235 9 30
A235 10 5
What would be the best approach to model such requirement.
Regards
Harris
I am developing HR data warehouse where one of the requirments is to calculate the number of days an employee is on vacation/leave for each month. From the source system I get emplyee ID , Vacation start date and vacation end date. if vacation period span over only one month I can calculate the date difference but in case vacation starts in 1 month and end in next month or even the period is over more than 2 months how can I model such a fact table so I report correct number of days for each month.
Example
For employee ID A234 the vacation start date is 21st September 2013 and end date is 7th October 2013 I need to report as follows:
EMPID MONTH NrofDays
A234 9 10
A234 10 7
Similarly for employee A235 the vacation start date is 25th August 2013 and end date is 5th OCtober 2013 so I need to report it as:
EMPID MONTH NrofDays
A235 8 7
A235 9 30
A235 10 5
What would be the best approach to model such requirement.
Regards
Harris
Last edited by grahan007 on Fri Oct 11, 2013 10:00 am; edited 1 time in total (Reason for editing : Added another example)
grahan007- Posts : 18
Join date : 2009-05-26
Similar topics
» Modeling Employee and Employee Role dimension.
» days of hospitalization as a "dynamic Measure"
» Employee Dimension and Employee "Profile" Dimension?
» Employee Dimensions
» How can Model Employee Dimension?
» days of hospitalization as a "dynamic Measure"
» Employee Dimension and Employee "Profile" Dimension?
» Employee Dimensions
» How can Model Employee Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum