How to chose one employee of two working the same month same position

Question

I have an HR table H with information about employees and a period table with dates and all periods(months). I may have a situation when one person. I'm joining them because I need to have information about every month(for other tasks). But I may have situation when one person(A) leaves in the middle of the month, B person starts his position in the middle of the same month. In this case for the current month I have to consider that person A is working on this position. Could you please help me to do it? Thank you very much in advance!

    SELECT 
           ,H.[PERSON_ID]
           ,H.[TITLE]
           ,H.[START_DATE]
           ,H.[END_DATE]
           ,IsNUll(H.END_DATE, Cast(getdate() as date)) as ED
           ,H.[DESCRIPTION]
           ,Per.PeriodID
           ,Per.CalDateStart
           ,Per.CalDateEnd
 
         FROM H
                 inner join Period Per
                        on IsNUll(H.END_DATE, Cast(getdate() as date)) >= Per.CalDateStart
                        and H.START_DATE <= Cast(Per.CalDateEnd as date)
    Where 1 = 1
   and PERSON_ID in (73, 81)

Answer 1

To solve the problem:

1. Change the JOIN condition. If the employee's employment period encloses the Per.CalDateStart of one period, safely consider the employee was working during that period.

on IsNUll(H.END_DATE, Cast(getdate() as date)) >= Per.CalDateStart
   and H.START_DATE <= Per.CalDateStart

To further optimize:

1. Use RIGHT JOIN to retain every single period in your Per table, making sure that we "have information about every month for the other tasks".

2. Use ORDER BY to sort the periods, for two benefits:

   1. The result is in a clear chronicle order.
   2. If one period is falsely binded with two or more employees, making it standout visually, and easy to detect programmatically.

SELECT 
       ,H.[PERSON_ID]
       -- unchanged snippet --
FROM H
       right join Period Per
       on IsNUll(H.END_DATE, Cast(getdate() as date)) >= Per.CalDateStart
          and H.START_DATE <= Per.CalDateStart
Where 1 = 1
      and PERSON_ID in (73, 81)
ORDER BY Per.CalDateStart

-- Content below was appended on 2022-08-24 --

To aggregate, making one period binded with at most one empolyee:

1. Use GROUP BY to aggregate the rows of the same period (with identical Per.PeriodID) into just one row. This one row shall be the row that has, saying, the earliest H.[START_DATE].
   1. Your business logic in practice defines the actual aggregation condition to apply.

SELECT 
       ,H.[PERSON_ID]
       ,H.[TITLE]
       ,MIN(H.[START_DATE])  -- aggregation condition
       -- unchanged snippet --
      and PERSON_ID in (73, 81)
GROUP BY Per.PeriodID  -- aggregation
ORDER BY Per.CalDateStart

(Optional) To exclude the rows that have no empolyee binded:

1. Based on the total number of binded employees, add HAVING condition in the aggregation.

-- unchanged snippet --
GROUP BY Per.PeriodID
HAVING COUNT(H.[PERSON_ID]) > 0
ORDER BY Per.CalDateStart

-- Content below was appended on 2022-08-25 --

To summarize:

SELECT 
       ,H.[PERSON_ID]
       ,H.[TITLE]
       ,MIN(H.[START_DATE])
       ,H.[END_DATE]
       ,IsNUll(H.END_DATE, Cast(getdate() as date)) as ED
       ,H.[DESCRIPTION]
       ,Per.PeriodID
       ,Per.CalDateStart
       ,Per.CalDateEnd
FROM H
       right join Period Per
       on IsNUll(H.END_DATE, Cast(getdate() as date)) >= Per.CalDateStart
          and H.START_DATE <= Per.CalDateStart
Where 1 = 1
      and PERSON_ID in (73, 81)
GROUP BY Per.PeriodID
HAVING COUNT(H.[PERSON_ID]) > 0
ORDER BY Per.CalDateStart