How can I select the employees who worked the longest time together on one project?

Question

I have a table with an example name Employees in which I save the id of every employee, the project id they are working on or has worked on and the date in which every employee started working on the project and the date they finished working on it.

EmployeeID -   ProjectID   -    DateStart   -    DateEnd
-------------------------------------------------------------
1          -     100       -    22/11/2017   -   14/12/2017 
2          -     101       -    02/01/2017   -   19/10/2017
3          -     102       -    05/08/2017   -   09/07/2017
4          -     102       -    04/03/2017   -   12/03/2017
5          -     100       -    06/01/2017   -   22/12/2017
6          -     100       -    16/01/2017   -   22/12/2017
7          -     100       -    26/01/2017   -   22/07/2018
7          -     102       -    12/01/2017   -   22/12/2017
3          -     100       -    04/01/2017   -   20/11/2018
5          -     102       -    03/01/2017   -   22/10/2018

I need to write a select query to find the couple of employees who worked the longest time together on a Project. Could you explain how this should happen? The hardest thing for me is how to detect if two employees was working on one project at the same time - that's the reason I didn't post any 'form of attempt' because all my attempt is going to self inner join such as

SELECT 
    A.EmployeeID, A.ProjectID, A.DateFrom, B.DateTo 
FROM 
    Employees A
INNER JOIN 
    Employees B on A.ProjectID = B.ProjectID

Answer 1

Try this, please:

    select 1 as EmployeeID,100 as ProjectID,cast (convert(datetime, '11-22- 
    2017',101) as date) as DateStart,cast (convert(datetime, '12-14-2017',101) as 
    date) as DateEnd into #Test

    insert into #Test values
    (2,101,'01/02/2017','10/19/2017'),
    (3,102,'08/05/2017','07/09/2017'),
    (4,102,'03/04/2017','03/12/2017'),
    (5,100,'01/06/2017','12/22/2017'),
    (6,100,'01/16/2017','12/22/2017'),
    (7,100,'01/26/2017','07/22/2018'),
    (7,102,'01/12/2017','12/22/2017'),
    (3,100,'01/04/2017','11/20/2018'),
    (5,102,'01/03/2017','10/22/2018')



     select distinct *  ,  DATEDIFF(dd, srt_date ,End_date  ) as 
     Max_No_Days_Worked_Togather
     from (
    select 
    t1.EmployeeID as t1_EmployeeID, t1.ProjectID as t1_ProjectID    , t1.DateStart   
    as t1_DateStart , 
    t1.DateEnd as t1_DateEnd,   t.EmployeeID as t_EmployeeID,   t.ProjectID as 
    t_ProjectID ,t.DateStart as t_DateStart,
    t.DateEnd as t_DateEnd,
    case 
    when t1.DateStart  > t.DateStart then t1.DateStart
    when t1.DateStart  < t.DateStart then t.DateStart end as srt_date,
    case 
    when t1.DateEnd  > t.DateEnd then t.DateEnd
    when t1.DateEnd  < t.DateEnd then t1.DateEnd end as End_date
    from #Test t1
    left join #Test t on t1.ProjectID=t.ProjectID and t1.EmployeeID<>t.EmployeeID
    )s
    order by  DATEDIFF(dd, srt_date ,End_date  )  desc 

Answer 2

Assuming that employees work on a project for only one period of time, you can do a self-join and aggregation:

select top (1) e1.projectId, e1.employeeId, e2.employeeId
from employees e1 join
     employees e2
     on e1.projectId = e2.projectId and
        e1.employeeId < e2.employeeId 
group by e1.employeeId, e2.employeeId
order by datediff(day,
                  (case when e1.datestart > e2.datestart then e1.datestart else e2.datestart end),
                  (case when e2.dateend < e1.dateend then e2.dateend else e1.dateend end)
                 ) desc;

Answer 3

First join the table with itself on common projectids and the employeeid of one larger than the other's. That gets you the interesting pairs of employees and the projects both of that pair had been working on. Then take the greater one of both their datestarts and the lesser one of both their dateends. Filter for those where the start is less than the end. Use datediff() to get the duration in days between these two dates. Use rank() with an ORDER BY the duration between the dates descending. Like that, the records with the longest duration will get a rank of 1 assigned. Now select from that set all the records where the rank is equal to 1.

SELECT x.employeeid1,
       x.employeeid2,
       x.projectid,
       x.datestart,
       x.dateend,
       x.days
       FROM (SELECT t1.employeeid employeeid1,
                    t2.employeeid employeeid2,
                    t1.projectid,
                    CASE
                      WHEN t1.datestart > t2.datestart THEN
                        t1.datestart
                      ELSE
                        t2.datestart
                    END datestart,
                    CASE
                      WHEN t1.dateend < t2.dateend THEN
                        t1.dateend
                      ELSE
                        t2.dateend
                    END dateend,
                    datediff(day,
                             CASE
                               WHEN t1.datestart > t2.datestart THEN
                                 t1.datestart
                               ELSE
                                 t2.datestart
                             END,
                             CASE
                               WHEN t1.dateend < t2.dateend THEN
                                 t1.dateend
                               ELSE
                                 t2.dateend
                             END) days,
                    rank() OVER (ORDER BY datediff(day,
                                                   CASE
                                                     WHEN t1.datestart > t2.datestart THEN
                                                       t1.datestart
                                                     ELSE
                                                       t2.datestart
                                                   END,
                                                   CASE
                                                     WHEN t1.dateend < t2.dateend THEN
                                                       t1.dateend
                                                     ELSE
                                                       t2.dateend
                                                   END) DESC) r
                    FROM elbat t1
                         INNER JOIN elbat t2
                                    ON t2.projectid = t1.projectid
                                       AND t2.employeeid > t1.employeeid
                    WHERE CASE
                            WHEN t1.datestart > t2.datestart THEN
                              t1.datestart
                            ELSE
                              t2.datestart
                          END
                          <
                          CASE
                            WHEN t1.dateend < t2.dateend THEN
                              t1.dateend
                            ELSE
                              t2.dateend
                          END) x
       WHERE x.r = 1;

db<>fiddle