1

I have the following data:

CREATE TABLE Employee (
  ID INT,
  LastName VARCHAR(50),
  FirstName VARCHAR(50)
);

CREATE TABLE EmploymentRelationship (
  ID INT,
  StartDate DATETIME,
  LastAffidavit CHAR(6),
  LastPayday CHAR(6),
  IsMainRelationship BIT
);


INSERT INTO Employee (ID, LastName, FirstName)
VALUES
  (1, 'BORDIGONI', 'FERNANDO SERGIO'),
  (2, 'BLANCO', 'ANTONIO'),
  (3, 'CHIRICHIGNO', 'PABLO NATANAEL'),
  (4, 'VIALE', 'MIRIAM NORA');

INSERT INTO EmploymentRelationship (ID, StartDate, LastAffidavit, LastPayday, IsMainRelationship)
VALUES
  (1, '2012-10-01 00:00:00.000', 202212, 202212, 0),
  (1, '2012-10-01 00:00:00.000', 202212, 202211, 1),
  (1, '2012-10-01 00:00:00.000', 202212, 202212, 0),
  (2, '2018-05-01 00:00:00.000', 202301, 202212, 1),
  (2, '2018-05-01 00:00:00.000', 202301, 202212, 0),
  (2, '2020-09-01 00:00:00.000', 202301, 202212, 0),
  (3, '2019-12-01 00:00:00.000', 202212, 202212, 0),
  (3, '2019-12-01 00:00:00.000', 202212, 202212, 1),
  (3, '2019-12-01 00:00:00.000', 202212, 202212, 0),
  (3, '2019-12-01 00:00:00.000', 202212, 202212, 0),
  (3, '2020-08-01 00:00:00.000', 202212, 202212, 0),
  (4, '2013-01-01 00:00:00.000', 202212, 202212, 0),
  (4, '2013-01-01 00:00:00.000', 202212, 202212, 0),
  (4, '2013-01-01 00:00:00.000', 202212, 202212, 1);

DB Fiddle (sample data and what I tried so far)

Date columns are the StartDate (datetime), LastAffidavit, and LastPayday (both char), in which the employee started their employment relationship.

Last column marks which is the main employment relationship (one employee can have several employers) with "1" value. This main relationship has to be the last date for every employee.

  • First ID has its last payment in November 2022, so it's not ok to be the main.
  • Second ID has a more recent start date that is not the main ("0" value).
  • The same for third ID.
  • The last ID has all the same values. If all dates are equal, it is indifferent which relationship is the same.

I need to get every employee (only one row) whose latest relationship is not the main. If the values of all three date columns are the same, exclude that one employee (like ID 4).

In desired output I need excluded ID 4 for the given reason above (all dates are the same).

It can return a row with value 1 or 0, I just need the ID of the employee, excluding the mentioned.

Desired output:

ID LastName FirstName StartDate LastAffidavit LastPayday IsMainRelationship
1 BORDIGONI FERNANDO SERGIO 2012-10-01 00:00:00.000 202212 202212 False
2 BLANCO ANTONIO 2020-09-01 00:00:00.000 202301 202212 False
3 CHIRICHIGNO PABLO NATANAEL 2020-08-01 00:00:00.000 202212 202212 False

(Last column can also be 0 and 1)

I am currently using SQL Server. It has more than 60.000 employees.

1 Answer 1

1

You need to select all rows which are not MainRelationship where there is not a more recent one but there is at least one less recent.

SELECT DISTINCT E.ID, LastName, FirstName,
StartDate,
LastAffidavit,
LastPayday,
IsMainRelationship
FROM Employee E JOIN EmploymentRelationship ER ON (E.ID=ER.ID)
WHERE IsMainRelationship = 0
AND NOT EXISTS (select id from EmploymentRelationship ER2 where ER2.id=E.id and (ER2.StartDate > ER.StartDate OR ER2.LastAffidavit > ER.LastAffidavit OR ER2.LastPayDay > ER.LastPayday))
AND EXISTS (select id from EmploymentRelationship ER3 where ER3.id=E.id and (ER3.StartDate < ER.StartDate OR ER3.LastAffidavit < ER.LastAffidavit OR ER3.LastPayDay < ER.LastPayday))

DBFiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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