Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
sql_fb
stackoverflow_mysql_demo_data
Joins outside XML element
Sequence object -- New Feature of 2012 sql server
BC160401693
Lab assessment 2
store
Microsoft SQL Server in 10 mn - Lesson 14 Joining Tables samples...
Terceiro
2021-03-06_LeetCodeSQL