Finding aggregated values based on dates in another table

Question

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

Answer 1

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

• Brian Giroux: This solution works perfectly, both on the example database tables provided in the question and in my actual database. It's also much simpler than what I was trying to do. Thank you.