Find effective count of employees within date range for each calendar week, Sql Server

Find effective count of employees within date range for each calendar week

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version';

-- Challenge: Find count of employees in date range of contract period
-- 
-- Solution: use generated(!) help data representing the wanted calendar weeks
--           here: year of calendar week is ignored
--           here: calendar week without contract data doesn't get displayed

-- demo data
create table workerContract(id bigint primary key, employmentStart  date, employmentEnd date);
insert into workerContract values (1,'2020-01-01', '2020-04-12');
insert into workerContract values (2,'2020-01-25', '2020-03-09');
insert into workerContract values (3,'2020-03-01', '2020-03-31');

-- try sql calendar week 'datepart' function on worker contract periods
--         select datepart(ww, c.employmentStart) , datepart(ww, c.employmentEnd) from workerContract c;

-- use ms specific recursive row generator function in order to get the week numbers
--     since no straight forward row generator functions available in sql server
--     how ever, this recursive function is somehow limited in depth, optional limit shifting possible 
WITH InfiniteRows (RowNumber) AS (             -- define CTE (Common Table Expression)
   SELECT 1 AS RowNumber                       -- Anchor member definition                  
   UNION ALL
   -- Recursive member definition, to get more than one record (in this example up to 53 records required)
   SELECT a.RowNumber + 1    AS RowNumber
   FROM   InfiniteRows a
   WHERE  a.RowNumber < 54
)
-- Statement that executes the CTE
SELECT RowNumber as KW, count(c.id) as countEmp_
  FROM infiniteRows i, workerContract c
                                                -- limit result to effictive contract dates of workers
 WHERE i.Rownumber between datepart(ww, c.employmentStart) and  datepart(ww, c.employmentEnd) 
 GROUP BY rownumber;
GO

drop table workerContract;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development