I have to build the Exceptions Report to catch Overlaps or Gaps. The dataset has clients and assigned supervisors with start and end dates of supervision.

CREATE TABLE Report
(Id INT, ClientId INT, ClientName VARCHAR(30), SupervisorId INT, SupervisorName 
VARCHAR(30), SupervisionStartDate DATE, SupervisionEndDate DATE);

INSERT INTO Report
VALUES
(1, 22, 'Client A', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(2, 22, 'Client A', 44, 'Supervisor B', '2022-05-01', '2022-08-23'),
(3, 22, 'Client A', 55, 'Supervisor C', '2022-08-24', NULL),
(4, 23, 'Client B', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(5, 23, 'Client B', 44, 'Supervisor B', '2022-04-30', '2022-08-23'),
(6, 24, 'Client C', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(7, 24, 'Client C', 44, 'Supervisor B', '2022-05-01', '2022-08-23'),
(8, 24, 'Client C', 55, 'Supervisor C', '2022-07-22', '2022-10-25'),
(9, 25, 'Client D', 33, 'Supervisor A', '2022-01-01', '2022-04-30'),
(10, 25, 'Client D', 44, 'Supervisor B', '2022-07-23', NULL)

SELECT * FROM Report

'Valid' status should be assigned to all rows associated with Client if no Gaps or Overlaps present, for example:

Client A has 3 Supervisors - Supervisor A (01/01/2022 - 04/30/2022), Supervisor B (05/01/2022 - 08/23/2022) and Supervisor C (08/24/2022 - Present).

'Issue Found' status should be assigned to all rows associated with Client if any Gaps or Overlaps present, for example:

Client B has 2 Supervisors - Supervisor A (01/01/2022 - 04/30/2022) and Supervisor B (04/30/2022 - 08/23/2022).

Client C has 3 Supervisors - Supervisor A (01/01/2022 - 04/30/2022), Supervisor B (05/01/2022 - 08/23/2022) and Supervisor C (07/22/2022 - 10/25/2022).

These are examples of the Overlap.

Client D has 2 Supervisors - Supervisor A (01/01/2022 - 04/30/2022) and Supervisor B (07/23/2022 - Present).

This is the example of the Gap.

The Output I need:

I added some columns that might be helpful, but don't know how to accomplish the main goal. However, I noticed, that if the first record in the [Diff Between PreviousEndDate And SupervisionStartDate] column is NULL and all other = 1, then it will be Valid.

SELECT 

     Report.*,

     ROW_NUMBER() OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)) AS [ClientRecordNumber],
     COUNT(*) OVER (PARTITION BY Report.ClientId) AS [TotalNumberOfClientRecords],

     DATEDIFF(DAY, Report.SupervisionStartDate, Report.SupervisionEndDate) AS SupervisionAging,

     LAG(Report.SupervisionStartDate) OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)) AS PreviousStartDate,
     LAG(Report.SupervisionEndDate) OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)) AS PreviousEndDate,
     LEAD(Report.SupervisionStartDate) OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)) AS NextStartDate,
     LEAD(Report.SupervisionEndDate) OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)) AS NextEndDate,

     DATEDIFF(dd, LAG(Report.SupervisionEndDate) OVER (PARTITION BY Report.ClientId ORDER BY COALESCE(Report.SupervisionStartDate, Report.SupervisionEndDate)), Report.SupervisionStartDate) AS [Diff Between PreviousEndDate And SupervisionStartDate]

FROM Report

CodePudding user response：

One approach:

1. Use the additional LAG parameters to provide a default value for when its null, and make that value a valid value i.e. 1 day before the StartDate
2. Use a CTE to calculate the difference in days between the StartDate and previous EndDate.
3. Then use a second CTE to determine for any given client whether there is an issue.
4. Finally display your desired results.

WITH cte1 AS (
    SELECT 
        R.*
        , DATEDIFF(day, LAG(R.SupervisionEndDate,1,dateadd(day,-1,R.SupervisionStartDate)) OVER (PARTITION BY R.ClientId ORDER BY COALESCE(R.SupervisionStartDate, R.SupervisionEndDate)), R.SupervisionStartDate) AS Diff
    FROM Report R
), cte2 AS (
    SELECT *
      , MAX(Diff) OVER (PARTITION BY ClientId) MaxDiff
      , MIN(Diff) OVER (PARTITION BY ClientId) MinDiff
    FROM cte1
)
SELECT Id, ClientId, ClientName, SupervisorId, SupervisorName, SupervisionStartDate, SupervisionEndDate
    --, Diff, MaxDiff, MinDiff -- Debug
    , CASE WHEN MaxDiff = 1 AND MinDiff = 1 THEN 'Valid' ELSE 'Issue Found' END [Status]
FROM cte2
ORDER BY Id;

Notes:

1. Use the fullname of the datepart you are diff-ing - its much clearer and easier to maintain.
2. Use short, relevant, table aliases to reduce the code.