Grouping users by 90 day periods in SQL

I need to get the last 90 hired employees that were terminated for each of the last 4 weeks. It's a rolling report.

The employee table is like so:

id      hired_date       term_date
3        07/1/2011        09/01/2011
4        07/18/2011        NULL    (NULL means still active) 
5        01/20/2009       08/23/2011
6        05/30/2011       8/22/2011
7        7/20/2011        7/23/2011

The report would be in this format. Id #4,5 would be ignored since #4 is still active and #5 was hired before the 90 day period.

Week ending     Terminated employees hired within past 90 days
09/03/2011        2   --(id 3,7)
8/27/2011         2   --(id 6,7)
..
..

• So the first row for 9/03 is the number of terminated employees hired within the past 90 days of 9/03/2011 (going back to 06/05/2011). This doesn't include id #6 because employee was hired before 6/05.
• The second row for 8/27 week is the same but from the range of 8/27 to 5/29.

I have a date table but it only contains week_start_date, week_end_date, and week_number. Do I need to create one that contains the 90 day periods?

I'm stuck how I can do this for only employees 开发者_如何学Cwithin the past 90 days and then calculate that for each of the past 4 weeks.

SQL Server 2008

edit: I think I'm close. I'm testing it right now. All_termed_employees is a listing of terminated employees within any date range. The weeks table now contains the ninety_begin_date and ninety_end_date for each week associated with it.

select wk.ninety_end, count(h.id)
FROM @weeks wk
LEFT JOIN all_termed_employees h 
    ON h.hire_date <= wk.ninety_end and h.hire_date >= wk.ninety_begin
          and .termination_date <= wk.ninety_end AND h.termination_date >= wk.ninety_begin
ORDER BY d.id
GROUP BY wk.ninety_end

;WITH n(n) AS 
      -- just 4 rows - makes it easy to extend to 5 weeks, 6 weeks, etc.
      ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ),

d(dt) AS
      -- single row with the end of the current week
      -- this could be a variable but I get a lot of flack for not inlining
      ( SELECT dt = CONVERT(DATE, DATEADD(DAY, 
          7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP))),

w(dt) AS 
      -- get the end of each week based on the rows in n
      ( SELECT DATEADD(WEEK, -n.n, d.dt) FROM n CROSS JOIN d )

SELECT 
    w.dt, SUM(CASE 
    WHEN e.term_date >= DATEADD(DAY, -90, w.dt) 
    AND e.term_date  <  DATEADD(DAY, 1,   w.dt) 
    AND e.hired_date >= DATEADD(DAY, -90, w.dt) 
    AND e.hired_date <  DATEADD(DAY, 1,   w.dt)  
    THEN 1 ELSE 0 END)
FROM dbo.Employees AS e
CROSS JOIN w
GROUP BY w.dt
ORDER BY w.dt DESC; 

How about something like this?

EMP simulates your employee table. The PAST_FOUR_WEEKS is a simple 4 row in-memory table that identifies the reporting periods. I then calculate the difference between hire and term date and if it's less than or equal to 90, we count it as 1 (threshold met) or 0). I then sum all the threshold's met for the given time period

; WITH EMP (id, hired_date, term_date) AS
(
    select 3, CAST('2011-07-01' AS datetime), CAST('2011-09-01' AS datetime)
    union all select 4, '2011-06-18', null
    union all select 5, '01/20/2009','08/23/2011'
    union all select 6, '05/30/2011','8/22/2011'
    union all select 7, '7/20/2011','7/23/2011'
)
, PAST_FOUR_WEEKS (period, period_rank) AS
(
    -- magic goes here to determine end of week
    SELECT CURRENT_TIMESTAMP, 1
    UNION ALL SELECT dateadd(week, -1, CURRENT_TIMESTAMP) , 2
    UNION ALL SELECT dateadd(week, -2, CURRENT_TIMESTAMP) , 3
    UNION ALL SELECT dateadd(week, -3, CURRENT_TIMESTAMP) , 4
)
, HIRED_TERMED_DIFF AS
(
    SELECT
        *
    ,   DATEDIFF(d, E.hired_date, coalesce(E.term_date, '9999-12-31T23:59:59.997')) AS duration
    ,   CASE WHEN DATEDIFF(d, E.hired_date, coalesce(E.term_date, '9999-12-31T23:59:59.997')) < 91 THEN 1 ELSE 0 END AS threshold_met
    FROM
        EMP E 
)
SELECT
    PFW.period
,   SUM(HTD.threshold_met) AS [Terminated employees hired within past 90 days]
FROM 
    PAST_FOUR_WEEKS PFW
    LEFT OUTER JOIN
        HIRED_TERMED_DIFF HTD
        ON HTD.hired_date BETWEEN DATEADD(day, -90, PFW.period) AND PFW.period
GROUP BY
    PFW.period
ORDER BY
    1 DESC
,   2

Sample output

period                    Terminated employees hired within past 90 days
2011-09-01 14:46:29.243   2
2011-08-25 14:46:29.243   3
2011-08-18 14:46:29.243   3
2011-08-11 14:46:29.243   3