SQL Query to Find Overlapping Dates in Separate Rows and Columns-CodePudding

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.

CodePudding user response：

You can get the dates on a row with a conditional MAX OVER.
And then it becomes easy to compare the dates per phase.

SELECT REC_ID, ITEM_ID, PHASE_NAME 
, PHASE_A , PHASE_B , PHASE_C , PHASE_D 
, CASE 
  WHEN PHASE_NAME LIKE '%B' AND B < A
  THEN 'PhB Olps PhA' 
  ELSE 'Pass' 
  END AS PH_B_OL_PH_A
, CASE 
  WHEN PHASE_NAME LIKE '%C' AND C < A
  THEN 'PhC Olps PhA' 
  ELSE 'Pass' 
  END AS PH_C_OL_PH_A
, CASE 
  WHEN PHASE_NAME LIKE '%D' AND D < A
  THEN 'PhD Olps PhA' 
  ELSE 'Pass' 
  END AS PH_D_OL_PH_A
, CASE 
  WHEN PHASE_NAME LIKE '%C' AND C < B
  THEN 'PhC Olps PhB' 
  ELSE 'Pass' 
  END AS PH_C_OL_PH_B
, CASE 
  WHEN PHASE_NAME LIKE '%D' AND D < B
  THEN 'PhD Olps PhB' 
  ELSE 'Pass' 
  END AS PH_D_OL_PH_B
, CASE 
  WHEN PHASE_NAME LIKE '%D' AND D < C
  THEN 'PhD Olps PhC' 
  ELSE 'Pass' 
  END AS PH_D_OL_PH_C
FROM
(
  SELECT *
  , MAX(CASE WHEN PHASE_NAME LIKE '%A' THEN PHASE_A END) OVER (PARTITION BY REC_ID) AS A
  , MAX(CASE WHEN PHASE_NAME LIKE '%B' THEN PHASE_B END) OVER (PARTITION BY REC_ID) AS B
  , MAX(CASE WHEN PHASE_NAME LIKE '%C' THEN PHASE_C END) OVER (PARTITION BY REC_ID) AS C
  , MAX(CASE WHEN PHASE_NAME LIKE '%D' THEN PHASE_D END) OVER (PARTITION BY REC_ID) AS D
  FROM #temp
) q
ORDER BY rec_id, phase_name;

REC_ID ITEM_ID PHASE_NAME PHASE_A PHASE_B PHASE_C PHASE_D PH_B_OL_PH_A PH_C_OL_PH_A PH_D_OL_PH_A PH_C_OL_PH_B PH_D_OL_PH_B PH_D_OL_PH_C

0001 ab05 Phase A 2022-02-01 null null null Pass Pass Pass Pass Pass Pass

0001 ab10 Phase B null 2022-02-02 null null Pass Pass Pass Pass Pass Pass

0001 ab03 Phase C null null 2022-01-01 null Pass PhC Olps PhA Pass PhC Olps PhB Pass Pass

0001 ab99 Phase D null null null 2022-02-03 Pass Pass Pass Pass Pass Pass

0002 cn00 Phase A 2021-12-01 null null null Pass Pass Pass Pass Pass Pass

0002 cn34 Phase B null 2021-11-20 null null PhB Olps PhA Pass Pass Pass Pass Pass

0002 cn07 Phase C null null 2021-12-03 null Pass Pass Pass Pass Pass Pass

0002 cn55 Phase D null null null 2021-12-02 Pass Pass Pass Pass Pass PhD Olps PhC

REC_ID	ITEM_ID	PHASE_NAME	PHASE_A	PHASE_B	PHASE_C	PHASE_D	PH_B_OL_PH_A	PH_C_OL_PH_A	PH_D_OL_PH_A	PH_C_OL_PH_B	PH_D_OL_PH_B	PH_D_OL_PH_C
0001	ab05	Phase A	2022-02-01	null	null	null	Pass	Pass	Pass	Pass	Pass	Pass
0001	ab10	Phase B	null	2022-02-02	null	null	Pass	Pass	Pass	Pass	Pass	Pass
0001	ab03	Phase C	null	null	2022-01-01	null	Pass	PhC Olps PhA	Pass	PhC Olps PhB	Pass	Pass
0001	ab99	Phase D	null	null	null	2022-02-03	Pass	Pass	Pass	Pass	Pass	Pass
0002	cn00	Phase A	2021-12-01	null	null	null	Pass	Pass	Pass	Pass	Pass	Pass
0002	cn34	Phase B	null	2021-11-20	null	null	PhB Olps PhA	Pass	Pass	Pass	Pass	Pass
0002	cn07	Phase C	null	null	2021-12-03	null	Pass	Pass	Pass	Pass	Pass	Pass
0002	cn55	Phase D	null	null	null	2021-12-02	Pass	Pass	Pass	Pass	Pass	PhD Olps PhC

Demo on db<>fiddle here

CodePudding user response：

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