SQL Query to Find Overlapping Dates in Separate Rows and Columns

Question

I have a need to check Phase Dates that are stored in separate columns in a Table, to report when any Date from a follow-on Phase overlaps the prior Phase Date.

Below is information on what I am attempting with my actual data, but based on made-up data. Hoping that this will help, I have provided this as consumable test data, rather than listing data in an on-page Table format:

CREATE TABLE #temp
(
    REC_ID char(4) NOT NULL
    ,ITEM_ID CHAR(4) NOT NULL
    ,PHASE_NAME varchar(10) NOT NULL
    ,PHASE_A date NULL
    ,PHASE_B date NULL
    ,PHASE_C date NULL
    ,PHASE_D date NULL
    ,PRIMARY KEY (REC_ID, PHASE_NAME)
);
INSERT INTO #temp
VALUES ('0001', 'ab05', 'Phase A', '20220201', NULL, NULL, NULL)
    ,('0001', 'ab10', 'Phase B', NULL, '20220202', NULL, NULL)
    ,('0001', 'ab03', 'Phase C', NULL, NULL,'20220101', NULL)
    ,('0001', 'ab99', 'Phase D', NULL, NULL, NULL, '20220203')
    ,('0002', 'cn00', 'Phase A', '20211201', NULL, NULL, NULL)
    ,('0002', 'cn34', 'Phase B', NULL, '20211120', NULL, NULL)
    ,('0002', 'cn07', 'Phase C', NULL, NULL, '20211203', NULL)
    ,('0002', 'cn55', 'Phase D', NULL, NULL, NULL, '20211202');

Please note that the 'Item_ID' is a very long system generated Global Unique Identifer (GUID), so it is probably not a good candidate for sorting purposes.

Note that each of the Phase Dates are captured in their own column. As such, as an example, the 'Phase_B' column Date needs to be compared with the 'Phase_A' Column Date.

Finally, to make it even more complicated, the results need to be captured to their own columns to support the error validation reporting that is needed.

To help clarify this, I have attempted to detail below the type of target table output that I need below:

CREATE TABLE #tmptgt
(
    REC_ID char(4) NOT NULL
    ,ITEM_ID CHAR(4) NOT NULL
    ,PHASE_NAME varchar(10) NOT NULL
    ,PHASE_A date NULL
    ,PHASE_B date NULL
    ,PHASE_C date NULL
    ,PHASE_D date NULL
    ,PH_B_OL_PH_A varchar(14) NULL
    ,PH_C_OL_PH_A varchar(14) NULL
    ,PH_D_OL_PH_A varchar(14) NULL
    ,PH_C_OL_PH_B varchar(14) NULL
    ,PH_D_OL_PH_B varchar(14) NULL
    ,PH_D_OL_PH_C varchar(14) NULL
    ,PRIMARY KEY (REC_ID, PHASE_NAME)
);
INSERT INTO #tmptgt
VALUES ('0001', 'ab05', 'Phase A', '20220201', NULL, NULL, NULL, 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0001', 'ab10', 'Phase B', NULL, '20220202', NULL, NULL, 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0001', 'ab03', 'Phase C', NULL, NULL,'20220101', NULL, 'Pass', 'PhC Olps PhA', 'Pass', 'PhC Olps Ph', 'Pass', 'Pass')
    ,('0001', 'ab99', 'Phase D', NULL, NULL, NULL, '20220203', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0002', 'cn00', 'Phase A', '20211201', NULL, NULL, NULL, 'PhB Olps PhA', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0002', 'cn34', 'Phase B', NULL, '20211120', NULL, NULL, 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0002', 'cn07', 'Phase C', NULL, NULL, '20211203', NULL, 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass')
    ,('0002', 'cn55', 'Phase D', NULL, NULL, NULL, '20211202', 'Pass', 'Pass', 'Pass', 'Pass', 'Pass', 'PhD Olps PhC');

I am not certain if this type of combined overlapping date checking and feedback layout is possible, but that is what is required to support our reporting needs and I would certainly appreciate feedback.

Answer 1

The key is to gather up the dates into a single record, after which the validity tests may be more easily applied and combined with the original data. Try something like:

SELECT T.*,
    Check1 = CASE WHEN D.DateA < D.DateB THEN 'Pass' ELSE 'Oops' END,
    Check2 = CASE WHEN D.DateA < D.DateC THEN 'Pass' ELSE 'Oops' END,
    ...
FROM #temp T
JOIN (
    SELECT REC_ID,
        DateA = MAX(PHASE_A),
        DateB = MAX(PHASE_B),
        DateC = MAX(PHASE_C),
        DateD = MAX(PHASE_D)
    FROM #Temp
    GROUP BY REC_ID
) D ON D.REC_ID = T.REC_ID