I have a requirement to confirm how many employees were 'active' on a given date. The employee table which I'm using contains the following fields (among others):
1. Row_Start_Date (when an employee started with the company)
2. Row_End_Date (when an employee terminated with the company (if applicable))
What would be the best way to design this? For example, let's say an employee started on 1/1/2010 (Row_Start_Date) and is still employeed (Row_End_Date is null). If an analyst wants to see how many employees were active on 5/1/2010, how do I
design it so that this employee is counted. If I build a date dimension off of Row_Start_Date, then this employee would be reflected on 1/1/2010, but not on 5/1/2010.
Hopefully this makes sense, and I appreciate any assistance/suggestions in advance...
View Complete Post