0

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.

  • 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. – Vérace May 11 at 15:50
  • Have you tried anything? What didn't work as you expected? – mustaccio May 11 at 16:04
  • 1
    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! – Josh Darnell May 11 at 18:45
  • @JoshDarnell wouldn't this be something that would be asked on StackOverflow.com not here? – Edward May 12 at 0:32
  • @Edward I think it's a fuzzy line. The solution necessarily involves subqueries or apply, which is not completely basic. – Josh Darnell May 12 at 0:59
2

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.