Aggregate N-times (by partition) in one query? CTE?

Net hires for a date range

I have a set of employee data with these fields. termination date is null if they're still active.

id   |    hire date    | termination date
1           7/25/11     |         null
2           12/01/10    |  7/30/11
3           7/20        |  null
4           7/16        | 7/29/11

What I need to get is for a rolling 13 week period is:

week ending date     Net hires
07-17                   1
07-24                   1
07-31          -1

...

I've begun doing it with a CTE and then using unions because I gave up开发者_Python百科 trying to figure it out the better way. I have to do this for some other fields too so maybe there's a better way, maybe using pivot/unpivot?

Rough pseudocode of what I have now.

;with foo
as
(
select *
from employee
--joins and conditions
)
select @report_date, sum(case when hire date > report_date-7 and hire_date < report_date then 1 else 0 end)(

... etc

I'll take care of dates with no hires/terminations later.

The fact that you are putting all of these UNIONs together with a hard-coded column should tell you that you're missing a set somewhere. In this case it's the set of weeks. Put those in a table (permanent, temporary, or virtual via a CTE) and this query becomes trivial.

SELECT
    WKS.start_date,
    COUNT(E.employee_id)
FROM
    Report_Weeks WKS
LEFT OUTER JOIN Employees E ON
    E.hire_date > WKS.start_date AND
    E.hire_date < WKS.end_date
GROUP BY
    WKS.start_date
ORDER BY
    WKS.start_date

Since you refer to "net" hires I'm guessing that you want to work in the terminations as well. You can do that by turning it into a SUM(CASE...) where you use 1 for new hires, 0 for people outside of that week or who were hired then fired within the same week, and -1 for terminations.

As JNK points out, order matters in the CASE statement. Also, you can use the ELSE to handle the 0 values. I would suggest something like this:

CASE
    WHEN
        E.hire_date BETWEEN WKS.start_date AND WKS.end_date AND
        E.termination_date > WKS.end_date THEN 1
    WHEN
        E.termination_date BETWEEN WKS.start_date AND WKS.end_date AND
        E.hire_date < WKS.end_date THEN -1
    ELSE 0
END

Since I used BETWEEN there you need to make sure that your start date for one week isn't exactly the same as the end date for the previous week.