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)
    (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');

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

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
    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

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.