Employee Vacation days

View previous topic View next topic Go down

Employee Vacation days

Post  grahan007 on Fri Oct 11, 2013 9:57 am

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


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

View user profile

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum