I'm building a report that will catch all gaps and overlaps in the Responsible Start and End dates.

Idea is to have a column (Status), that will have 5 outputs (I understand, that there is a possibility of multiple scenarios for a record, but I need them to be assigned in the below order:

1. No Responsible - when no associated Responsible at all (ResponsibleId or ResponsibleName is NULL)
2. No Current Responsible - when across all the records associated with the Client, ResponsibleEndDate is not NULL (means current).
3. Overlap - when ResponsibleEndDate of the previous Responsible overlaps with ResponsibleStartDate of the following Responsible person.
4. Gap - when a gap between ResponsibleEndDate of the previous Responsible and ResponsibleStartDate of the following Responsible person.
5. Valid - when 1 day difference between ResponsibleEndDate of the previous responsible and ResponsibleStartDate of the following Responsible person.

CREATE TABLE example
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);

INSERT INTO example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)

SELECT * FROM example

Output I need:

The only condition I could achieve is the first one:

SELECT *,
      CASE      
        WHEN ResponsibleId IS NULL THEN 'No Responsible'      
      END AS [Status]      
FROM example

CodePudding user response：

This is a good start:

declare @example table
(ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);

INSERT INTO @example
VALUES
(123, 'John Smith', NULL, NULL, NULL, NULL),
(234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(345, 'Mary Tron', NULL, NULL, NULL, NULL),
(456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)
;WITH CTE AS
(
SELECT *,
    --ROW_NUMBER() OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) DN,
    LEAD(ResponsibleStartDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) NEXT_START_DATE,
    LAG(ResponsibleEndDate)OVER(PARTITION BY ClientName ORDER BY COALESCE(ResponsibleStartDate, ResponsibleEndDate)) PREV_END_DATE
FROM @example
)
select *,
    CASE 
        WHEN ResponsibleName IS NULL THEN 'No Responsible'
        WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate < PREV_END_DATE then 'OVERLAP'
        WHEN PREV_END_DATE IS NOT NULL AND DATEDIFF(dd,PREV_END_DATE, ResponsibleStartDate) = 1 then 'VALID'
        WHEN PREV_END_DATE IS NULL then 'VALID'
        WHEN PREV_END_DATE IS NOT NULL AND ResponsibleStartDate > PREV_END_DATE then 'GAP'
        ELSE 'CASE'
    end [STATUS]
from 
    CTE;