SQL & PL/SQL

Hi! I'm new, am hoping for some help with a particular query. I need to count the number of employees that were active at any given location on any given day. For each active employee, there would be one row for each day, as I need to be able to run the query "as of" a certain point in time. We are only interested in dates beginning 1-1-2011 going forward.

An example of a result set would be:

EMPNO DEPTNO LOCNO EMPSTATUS JOBNO HIRE_DATE TERM_DATE CHANGE_EFF_DATE COUNT_DATE COUNT
111 111 101 Active 111 21-may-2007 01-sep-2012 0
111 112 101 Active 123 21-may-2007 01-may-2012 01-sep-2012 1
222 202 102 Terminated 333 5-june-2010 1-sep-2012 01-sep-2012 1
333 303 301 Terminated 444 2-sep-2010 6-aug-2011 01-sep-2012 0
444 100 202 Active 222 1-jun-2010 01-sep-2012 1
Total active employees on 1-sep-2012 = 3

111 112 101 Active 123 21-may-2007 01-may-2012 02-sep-2012 1
222 202 102 Terminated 333 5-june-2010 1-sep-2012 02-sep-2012 0
444 100 202 Active 222 1-jun-2010 02-sep-2012 1
Total active employees on 2-sep-2012 = 2

In the above example, the "TERM_DATE" is actually the employee's last day of employment, so I would count them if the term date = count date. Also, Employee # 111 has changed job numbers, so is not counted twice on the same day. Employees terminated before the count date would not be counted. We would filter by job number, location, department in our BI tool.

I hope this explains what I am trying to do. I will try and clarify if need be. Thanks to anyone for any help!