How to compute the monthly headcount by taking account the entries in the month and the exits in the month

Other SQL Server Topics
1

Hello everybody, I would like to automatize the calculation of monthly headcount by taking into account the montlhy entries and the exits. The results need to be groupped by Activities, Regions, Countries and Societies

Description of my file :

Columns :
ID of the worker
Activity
Zone contract
Region
Country
Society
Contract start date (ex format : 08/04/2021 02:00:00)
Contract end date (ex format : 12/01/2020 01:00:00)

At the end, I would like to obtain this kind of table with :

  • the headcount by month ( 12 months)
  • the number of entries by month
  • the number of exits by month

Capture
Capture1795×371 29.7 KB

My objective is to be able to compare the monthly headcount with the montly entries and montlhy exits like this (with Tableau software) :

This is important for me, I am an intern. I have tried many procedures (select count, iif, case when etc...) but i couldn't get the result obtained with Excel using COUNT.IFS

Thanks you all in advance for your help.

2

If you are using Tableau to present the data - they should have a crosstab/pivot object available that you can use. If you must do that in SQL then lookup cross-tab queries for SQL Server. Here is one place to start: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns – SQLServerCentral

1 Like