SQL - Grouping with aggregation

Question

I have a table (TABLE1) that lists all employees with their Dept IDs, the date they started and the date they were terminated (NULL means they are current employees).

I would like to have a resultset (TABLE2) , in which every row represents a day starting since the first employee started( in the sample table below, that date is 20090101 ), till today. (the DATE field). I would like to group the employees by DeptID and calculate the total number of employees for each row of TABLE2.

How do I this query? Thanks for your help, in advance.

TABLE1

DeptID     EmployeeID   StartDate   EndDate
--------------------------------------------
001        123           20100101   20120101   
001        124           20090101   NULL
001        234           20110101   20120101

TABLE2

DeptID       Date      EmployeeCount
-----------------------------------
001          20090101     1
001          20090102     1
...          ...          1
001          20100101     2
001          20100102     2
...          ...          2
001          20110101     3
001          20110102     3
...          ...          3
001          20120101     1
001          20120102     1
001          20120103     1
...          ...          1

Answer 1

you need somthing along these lines.

SELECT  *
      , ( SELECT    COUNT(EmployeeID) AS EmployeeCount 
          FROM      TABLE1 AS f
          WHERE     t.[Date] BETWEEN f.BeginDate AND f.EndDate
        )
FROM    ( SELECT    DeptID
                  , BeginDate AS [Date]
          FROM      TABLE1
          UNION
          SELECT    DeptID
                  , EndDate AS [Date]
          FROM      TABLE1
        ) AS t

EDIT since OP clarified that he wants all the dates here is the updated solution I have excluded a Emplyee from Count if his job is ending on that date.But if you want to include change t.[Date] < f.EndDate to t.[Date] <= f.EndDate in the below solution. Plus I assume the NULL value in EndDate mean Employee still works for Department.

DECLARE @StartDate DATE = (SELECT MIN(StartDate) FROM Table1)
       ,@EndDate DATE = (SELECT MAX(EndDate) FROM Table1)

;WITH CTE AS 
(
SELECT DISTINCT DeptID,@StartDate AS [Date] FROM Table1
UNION ALL
SELECT c.DeptID, DATEADD(dd,1,c.[Date]) AS [Date] FROM CTE AS c
WHERE c.[Date]<=@EndDate
)
SELECT  * ,
        EmployeeCount=( SELECT    COUNT(EmployeeID) 
          FROM      TABLE1 AS f
          WHERE     f.DeptID=t.DeptID AND  t.[Date] >= f.StartDate
                    AND ( t.[Date] < f.EndDate OR f.EndDate IS NULL )
        )
FROM    CTE AS t
ORDER BY 1
OPTION  ( MAXRECURSION 0 )

here is SQL Fiddler demo.I have added another department and added an Employee to it.

http://sqlfiddle.com/#!3/5c4ec/1

Answer 2

This will work if you have a date look up table. You will need to specify the department ID. See it in action.

Query

SELECT d.dt, SUM(e.ecount) AS RunningTotal 
FROM dates d
INNER JOIN
  (SELECT b.dt, 
    CASE
      WHEN c.ecount IS NULL THEN 0
      ELSE c.ecount
    END AS ecount
  FROM dates b 
  LEFT JOIN
    (SELECT a.DeptID, a.dt, SUM([count]) AS ecount
    FROM
       (SELECT DeptID, EmployeeID, 1 AS [count], StartDate AS dt FROM TABLE1
        UNION ALL
        SELECT DeptID, EmployeeID, 
          CASE
            WHEN EndDate IS NOT NULL THEN -1
            ELSE 0
          END AS [count], EndDate AS dt FROM TABLE1) a
    WHERE a.dt IS NOT NULL AND DeptID = 1
    GROUP BY a.DeptID, a.dt) c ON c.dt = b.dt) e ON e.dt <= d.dt
GROUP BY d.dt

Result

|         DT | RUNNINGTOTAL |
-----------------------------
| 2009-01-01 |            1 |
| 2009-02-01 |            1 |
| 2009-03-01 |            1 |
| 2009-04-01 |            1 |
| 2009-05-01 |            1 |
| 2009-06-01 |            1 |
| 2009-07-01 |            1 |
| 2009-08-01 |            1 |
| 2009-09-01 |            1 |
| 2009-10-01 |            1 |
| 2009-11-01 |            1 |
| 2009-12-01 |            1 |
| 2010-01-01 |            2 |
| 2010-02-01 |            2 |
| 2010-03-01 |            2 |
| 2010-04-01 |            2 |
| 2010-05-01 |            2 |
| 2010-06-01 |            2 |
| 2010-07-01 |            2 |
| 2010-08-01 |            2 |
| 2010-09-01 |            2 |
| 2010-10-01 |            2 |
| 2010-11-01 |            2 |
| 2010-12-01 |            2 |
| 2011-01-01 |            3 |
| 2011-02-01 |            3 |
| 2011-03-01 |            3 |
| 2011-04-01 |            3 |
| 2011-05-01 |            3 |
| 2011-06-01 |            3 |
| 2011-07-01 |            3 |
| 2011-08-01 |            3 |
| 2011-09-01 |            3 |
| 2011-10-01 |            3 |
| 2011-11-01 |            3 |
| 2011-12-01 |            3 |
| 2012-01-01 |            1 |

Schema

CREATE TABLE TABLE1 (
  DeptID tinyint,
  EmployeeID tinyint,
  StartDate date,
  EndDate date)

INSERT INTO TABLE1 VALUES 
(1, 123, '2010-01-01', '2012-01-01'),
(1, 124, '2009-01-01', NULL),
(1, 234, '2011-01-01', '2012-01-01')

CREATE TABLE dates (
  dt date)

INSERT INTO dates VALUES 
('2009-01-01'), ('2009-02-01'), ('2009-03-01'), ('2009-04-01'), ('2009-05-01'), 
('2009-06-01'), ('2009-07-01'), ('2009-08-01'), ('2009-09-01'), ('2009-10-01'), 
('2009-11-01'), ('2009-12-01'), ('2010-01-01'), ('2010-02-01'), ('2010-03-01'), 
('2010-04-01'), ('2010-05-01'), ('2010-06-01'), ('2010-07-01'), ('2010-08-01'), 
('2010-09-01'), ('2010-10-01'), ('2010-11-01'), ('2010-12-01'), ('2011-01-01'), 
('2011-02-01'), ('2011-03-01'), ('2011-04-01'), ('2011-05-01'), ('2011-06-01'), 
('2011-07-01'), ('2011-08-01'), ('2011-09-01'), ('2011-10-01'), ('2011-11-01'), 
('2011-12-01'), ('2012-01-01')