SQL count(*) grouped by date range

Question

I have a table as follows:

DROP TABLE IF EXISTS #PersonnelRecord;
CREATE TABLE #PersonnelRecord
(
 person_id int NOT NULL,
 company_id int NOT NULL,
 entry_date date NOT NULL,
 exit_date date NOT NULL,
 CONSTRAINT PK_PersonnelRecord PRIMARY KEY (person_id)
);
INSERT INTO #PersonnelRecord
 (person_id, company_id, entry_date, exit_date)
VALUES
 (1, 24, '2004-03-17', '2010-12-31'),
 (2, 24, '2011-04-18', '2019-11-28'),
 (3, 25, '2017-02-10', '2019-10-20'),
 (4, 34, '2004-03-17', '2010-12-31'),
 (5, 24, '2004-03-17', '2999-01-01'),
 (6, 24, '2010-03-20', '2999-01-01');
SELECT *
FROM #PersonnelRecord pr
ORDER BY pr.person_id;

db<>fiddle link

enter image description here

So a table that shows when people have entered and exited a company. 2999年01月01日 means the person is still at the company.

I would like to add a count to each row of how many people are working at the company at time of the exit date. The result would be:

|-----|
| 3 |
|-----|
| 3 |
|-----|
| 1 |
|-----|
| 1 |
|-----|
| 2 |
|-----|
| 2 |
|-----|

How could I achieve that? Thanks for the help.

• Vérace: Hi and welcome to the forum! Could you please provide your table structure as DDL (in text in the question) and your data as DML (same deal) - you could also use a fiddle (dbfiddle.uk) - but be sure to post any data that's on the fiddle here also.
• mustaccio: Have you tried anything? What didn't work as you expected?
• Josh Darnell: Hi, Thomas - I've updated your question with the sort of demo script that @Vérace was describing. Having that should help make it easier for folks to answer your question. Please keep that approach in mind if / when you have further questions on the site!
• Edward: @JoshDarnell wouldn't this be something that would be asked on StackOverflow.com not here?
• Josh Darnell: @Edward I think it's a fuzzy line. The solution necessarily involves subqueries or apply, which is not completely basic.

Answer 1

You can use the APPLY operator to count the number of employees active at the termination date. As I looked at the question at first I missed that there were different companies, so once I added AND company_id = pr.company_id to the query I get the result you are expecting.

SELECT * FROM #PersonnelRecord pr
OUTER APPLY (
 SELECT count(person_id) as person_count FROM #PersonnelRecord
 WHERE pr.exit_date > entry_date 
 AND pr.exit_date <= exit_date 
 AND company_id = pr.company_id
 ) a
ORDER by person_id