The attached uses method #2 in The Cross Join Collection
SELECT * FROM [data$] d, [lookup$] l WHERE l.[date] >= d.[Hire Date] AND l.[date] <= CDATE(IIF(ISNULL(d.[Term Date]), DATE(), d.[Term Date]))
The attached spreadsheet has a lookup table of each date between Jan 1, 2002 and Dec 31, 2014.
The cross join increases number of rows from 103 to 182543.
Using extract increased the attached workbook from 22 KB to 55 KB.
The spreadsheet was opened with the Legacy Connection.
How kettan did this is how I'd do it...the reason why is that you want to "count" each employee N times, where N is the number of years/months/days that employee is active, and the easiest way for Tableau to do that is to give it enough records.
Here's a thread with a whole set of links on this:
I'm trying to achieve the same results, and whilst I can get the SQL to work, my employee data duplicates multiple times so it appears that our headcount is much higher than actual. Is someone able to tell me where I am going wrong?