Sql-server – Finding aggregated values based on dates in another table

sql servert-sql

I am using MS SQL SERVER. I have a table EMPLOYEE_ABSENCES that looks like this (it's not actually a temporary table, I made it into a temp table for this example):

CREATE TABLE #EMPLOYEE_ABSENCES
(
    EMPLOYEE_ID  VARCHAR(3),
    ABSENCE_DATE DATE
);

INSERT INTO #EMPLOYEE_ABSENCES
(
     EMPLOYEE_ID, 
     ABSENCE_DATE
)
VALUES
    ('111', '2020-03-24'),
    ('222', '2020-04-16'),
    ('111', '2020-07-13'),
    ('333', '2020-07-13'),
    ('111', '2020-10-19');
GO

I have another table that looks like this:

CREATE TABLE #EMPLOYEE_POSITIONS
(
    EMPLOYEE_ID         VARCHAR(3),
    POSITION_START_DATE DATE,
    POSITION_END_DATE   DATE,
    FTE                 NUMERIC(6, 5)
);

INSERT INTO #EMPLOYEE_POSITIONS
(
     EMPLOYEE_ID, 
     POSITION_START_DATE, 
     POSITION_END_DATE, 
     FTE
)
VALUES
    ('111', '2019-09-01', '2020-04-08', 0.75),
    ('333', '2020-01-01', '2020-09-18', 0.25),
    ('333', '2020-01-01', '2021-01-19', 0.5),
    ('222', '2020-01-19', '2021-01-19', 0.2),
    ('222', '2020-02-02', '2020-04-20', 0.3),
    ('111', '2020-05-20', '2020-07-14', 0.4),
    ('333', '2020-06-06', '2020-09-18', 0.25),
    ('111', '2020-07-15', '2021-01-20', 0.5),
    ('111', '2020-08-08', '2020-12-24', 0.3);
GO

As employees change position, their FTE changes.

An employee can hold more than one position, in which case, their FTE on any given date is the total of all FTE values they hold on that date.

For example, employee 111 holds a single position with a value of 0.5 from 2020-07-15 until 2020-08-07.

On 2020-08-08, she gains a second position with a value of 0.3 giving her a total FTE of 0.8 until on 2020-12-25 she loses her second position and returns to a single position with a value of 0.5

I need a query that will tell me the dates employees were absent in the year 2020, the employee number and their sum(FTE) on the date they were absent.

I am expecting a result like this for the above tables:

-- 2020-03-24, 111, 0.75
-- 2020-04-16, 222, 0.5
-- 2020-07-13, 111, 0.4
-- 2020-07-13, 333, 1
-- 2020-10-19, 111, 0.8

Note that two employees were absent on 13 July: employee 111 with an FTE of 0.4 on that date and employee 333 with a total FTE of 1 (0.25+0.5+0.25) on that date.

I think that the solution will involve creating a tally table of all dates in 2020, joining it to an aggregate of the EMPLOYEE_POSITIONS table to get the employees' total FTE for each day of the year, then joining that with the EMPLOYEE_ABSENCES table, but I can't seem to get it to work. Does anyone have any suggestions? Is there an easier way?

Thank you,

Brian

Best Answer

Something like this?

SELECT emp.Employee_Id,
       emp.Absence_date,
       SUM(FTE) AS FTE
  FROM #EMPLOYEE_ABSENCES AS emp
  JOIN #employee_Positions AS pos ON absence_date 
                             BETWEEN position_start_date AND position_end_date
                                 AND pos.Employee_Id = emp.Employee_Id
 GROUP BY emp.Employee_Id,
          emp.Absence_date
 ORDER BY Absence_date ASC
Related Question