MS SQL Server Identify Overlapping Dates

Transact-SQL
1

Greetings,

I have a Table containing multiple records with Phase Dates. I need to check to ensure each Phase Date does not overlap the prior Phase Dates.

My Table data is similar to the following:

REC_ID ITEM_ID PHASE_NAME PHASE_DATE Notes...
0001 01 Phase A 2022-02-01 First Ph of REC 0001
0001 02 Phase B 2022-02-02 PhB after Prior Ph means Pass
0001 03 Phase C 2022-01-01 PhC before Prior Phs means Fail
0001 04 Phase D 2022-02-03 PhD after Prior Phs means Pass
0002 05 Phase A 2021-12-01 First Ph of REC 0002
0002 06 Phase B 2021-11-20 PhB before Prior Phs means Fail
0002 07 Phase C 2021-12-03 PhC after Prior Phs means Pass
0002 08 Phase D 2021-12-02 PhD before PhC means Fail

etc...

The current query that I have is as follows:

SELECT		r.[REC_ID]
			,r.[ITEM_ID]
			,r.[PHASE_NAME]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_A] AS DATE)) >= MAX(CAST(r.[PHASE_B] AS DATE))) THEN 'PhB overlaps PhA' ELSE 'Pass' END) AS [PH-B_OL_PH-A]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_A] AS DATE)) >= MAX(CAST(r.[PHASE_C] AS DATE))) THEN 'PhC overlaps PhA' ELSE 'Pass' END) AS [PH-C_OL_PH-A]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_A] AS DATE)) >= MAX(CAST(r.[PHASE_D] AS DATE))) THEN 'PhD overlaps PhA' ELSE 'Pass' END) AS [PH-D_OL_PH-A]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_B] AS DATE)) >= MAX(CAST(r.[PHASE_C] AS DATE))) THEN 'PhC overlaps PhB' ELSE 'Pass' END) AS [PH-C_OL_PH-B]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_B] AS DATE)) >= MAX(CAST(r.[PHASE_D] AS DATE))) THEN 'PhD overlaps PhB' ELSE 'Pass' END) AS [PH-D_OL_PH-B]
			,(CASE	WHEN (MAX(CAST(r.[PHASE_C] AS DATE)) >= MAX(CAST(r.[PHASE_D] AS DATE))) THEN 'PhD overlaps PhC' ELSE 'Pass' END) AS [PH-D_OL_PH-C]
FROM		[MyTable] AS r
GROUP BY	r.[REC_ID] ,r.[ITEM_ID] ,r.[PHASE_NAME]

However, using this query, I get returned a "Pass" for every row. I think it's because MAX works on Rows rather than Columns.

Does anyone know the correct SQL code for comparing overlapping dates in such a scenario?

My environment in Microsoft SQL Server 2019.

Any suggestions are appreciated!

2

hi hope this helps

please click arrow for drop create sample data 
drop table #temp 
go 


create table #temp 
( 
REC_ID varchar(100),	ITEM_ID varchar(2), 	PHASE_NAME varchar(10),	PHASE_DATE	date 
) 

insert into #temp select '0001','01','Phase A','2022-02-01'	
insert into #temp select '0001','02','Phase B','2022-02-02'	
insert into #temp select '0001','03','Phase C','2022-01-01'	
insert into #temp select '0001','04','Phase D','2022-02-03'	
insert into #temp select '0002','05','Phase A','2021-12-01'	
insert into #temp select '0002','06','Phase B','2021-11-20'
insert into #temp select '0002','07','Phase C','2021-12-03'
insert into #temp select '0002','08','Phase D','2021-12-02'

go

; with cte as 
(
select *   from #temp a join (select rec_id as rec_id_1,min(item_id) as mnitem from #temp group by rec_id ) b on a.REC_ID = b.rec_id_1 and a.ITEM_ID = b.mnitem
) 
select a.*,case when a.PHASE_DATE = b.PHASE_DATE then 'First Record' when a.PHASE_DATE > b.PHASE_DATE then 'Pass' else 'FAIL' end   from #temp a join cte b on a.REC_ID= b.REC_ID 
go

image
image510×529 9.51 KB

3

You will get better answers if you provide consumable test data.

CREATE TABLE #t
(
	Rec_ID char(4) NOT NULL
	,Item_ID CHAR(2) NOT NULL
	,Phase_Name varchar(10) NOT NULL
	,Phase_Date date NOT NULL
	,Notes varchar(50) NOT NULL
	,PRIMARY KEY (Rec_ID, Item_ID)
);
INSERT INTO #t
VALUES ('0001', '01', 'Phase A', '20220201', 'First Ph of REC 0001')
	,('0001', '02', 'Phase B', '20220202', 'PhB after Prior Ph means Pass')
	,('0001', '03', 'Phase C', '20220101', 'PhC before Prior Phs means Fail')
	,('0001', '04', 'Phase D', '20220203', 'PhD after Prior Phs means Pass')
	,('0002', '05', 'Phase A', '20211201', 'First Ph of REC 0002')
	,('0002', '06', 'Phase B', '20211120', 'PhB before Prior Phs means Fail')
	,('0002', '07', 'Phase C', '20211203', 'PhC after Prior Phs means Pass')
	,('0002', '08', 'Phase D', '20211202 ', 'PhD before PhC means Fail');

The windowed functions will help with this:

SELECT Rec_ID, Item_ID, Phase_Name, Phase_Date, Notes
	-- Based on all previous rows within the partition
	,CASE
		WHEN Phase_Date <
			COALESCE
			(
				MAX(Phase_Date) OVER 
					(PARTITION BY Rec_ID ORDER BY Item_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
			, '1900')
		THEN 'Fail'
		ELSE 'Pass'
	END AS AllPorF
	--Based on last row within partition
	,CASE
		WHEN Phase_Date <
			COALESCE
			(
				LAG(Phase_Date) OVER (PARTITION BY Rec_ID ORDER BY Item_ID)
			, '1900')
		THEN 'Fail'
		ELSE 'Pass'
	END AS LastPorF
	--,MAX(Phase_Date) OVER (PARTITION BY Rec_ID ORDER BY Item_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
	--,LAG(Phase_Date) OVER (PARTITION BY Rec_ID ORDER BY Item_ID)
FROM #t
ORDER BY Rec_ID, Item_ID;
4

Hi @harishgg1 and @Ifor
Thanks very much for responding to my request for help so quickly. I had a bit of a late start to the day, so please excuse the delayed follow-up.
I just signed up to SQL Team yestersay, so I'm very new to using this forum. But I must say that I am very impressed with the prompt feedback and the excellent quality of both your responses.
Really appreciate the example code you have both provided. I will now work through this in my system and see if I can get it working with my actual data, rather than the example pseudo table structure that I provided.
Once I've done that, I'll report back on if I was competent enough to get it working in my scenario.
Thanks again so much for the prompt and detailed feedback to my predicament.
Cheers,
Wayne

5

Hi @Ifor
Thanks for the suggestion about providing consumable test data.
Being new to SQL Team and SQL coding in general, I was not sure how best to pose my question and provide supporting information. I am assuming that consumable test data mean the CREATE and INSERT process that you have documented.
So for future requests, I will be sure to use that process in my requests for support.
Cheers,
Wayne

6

OR

you could call me directly and we could discuss

i can remote desktop and do the changes or tell you what to do

Your Wish
Would be Glad to help

7

Greetings once again,

Here is a major update to my request. Having reviewed the prior responses and then checked my actual scenario, I have found that I initially very badly detailed my need.

My issue is that I am working in a secure environment and I therefore must generate sample pseudo-data to try to explain the need, My initial attempt was not good at all.

Therefore, below is updated information on exactly what I am attempting, with a close as possoble generic representation of my data. Per recommendation by @Ifor, I have first provided consumable test data below:

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');

(Rookie Note: I tried [CTRL] + K to display the above a Code, when editing in the browser, but can't tell if that worked. Hope it does.)

Please note that the 'Item_ID' is a very long system generated Global Unique Identifer (GUID), so the original two digit 'ITEM_ID' I provided was not a good example.

Then, 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 table output that i needed below:

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 PhB Olps PhA Pass Pass Pass Pass Pass
0002 cn34 Phase B NULL 2021-11-20 NULL NULL Pass 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

I am not certain if this type of combined overlapping date checking and feedback layout is possible, but I would certainly appreciate feedback on the topic.

This being my first request on this SQL Team Forum, I am already extremely impressed with the feedback that I have received.

Cheers,

Wayne

8

Please also provide the expected output as a create insert

create part is called DDL Data definition
insert part is called DML Data manipulation.

The image is nice but it is not useable which makes devs here hesitate to help you because it creates more work for devs as folks are busy. help us help you like you did with the sample data

9

Hi @yosiasz

Thanks for the heads-up on what's expected. Let me see if I can do that. I should have thought that through, as it was quite difficult to try and detail that Table as I did.

My SQL skills as not the best, but I think I've generated what you have requested 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');

Additionally, I think I managed to get the code format working this time.

Hopefully, this will help.

1 Like
10

I am not convinced at either the table structure or the results.
Why have multiple date columns?
Why is the fail text the same as the column name?

WITH MaxDatesSoFar
AS
(
	SELECT REC_ID, ITEM_ID, PHASE_NAME, PHASE_A, PHASE_B, PHASE_C, PHASE_D
		,MAX(PHASE_A) OVER (PARTITION BY REC_ID ORDER BY PHASE_NAME) AS MaxA
		,MAX(PHASE_B) OVER (PARTITION BY REC_ID ORDER BY PHASE_NAME) AS MaxB
		,MAX(PHASE_C) OVER (PARTITION BY REC_ID ORDER BY PHASE_NAME) AS MaxC
		,MAX(PHASE_D) OVER (PARTITION BY REC_ID ORDER BY PHASE_NAME) AS MaxD
	FROM #temp
)
SELECT REC_ID, ITEM_ID, PHASE_NAME, PHASE_A, PHASE_B, PHASE_C, PHASE_D
	--,MaxA,MaxB,MaxC,MaxD
	,CASE WHEN MaxB < MaxA AND PHASE_NAME = 'Phase B' THEN 'PhB Olps PhA' ELSE 'Pass' END AS PH_B_OL_PH_A
	,CASE WHEN MaxC < MaxA AND PHASE_NAME = 'Phase C' THEN 'PhC Olps PhA' ELSE 'Pass' END AS PH_C_OL_PH_A
	,CASE WHEN MaxD < MaxA AND PHASE_NAME = 'Phase D' THEN 'PhD Olps PhA' ELSE 'Pass' END AS PH_D_OL_PH_A
	,CASE WHEN MaxC < MaxB AND PHASE_NAME = 'Phase C' THEN 'PhC Olps PhB' ELSE 'Pass' END AS PH_C_OL_PH_B
	,CASE WHEN MaxD < MaxB AND PHASE_NAME = 'Phase D' THEN 'PhD Olps PhB' ELSE 'Pass' END AS PH_D_OL_PH_B
	,CASE WHEN MaxD < MaxC AND PHASE_NAME = 'Phase D' THEN 'PhD Olps PhC' ELSE 'Pass' END AS PH_D_OL_PH_C
FROM MaxDatesSoFar;
1 Like
11

Hi @Ifor,

The data is derived from a Project Management System that stores the records as Tasks with Start and End Dates. So initially, all Dates are in the same column.

However, aside from the overlapping Date checks, we have many other checks (e.g.Missing Phase Dates, Prior and Subsequent Phase gaps, Contiguous Phase Checks, Duplicate Phase checks, etc.). Additionally, there are Sub-Phases that are tracked.

So we configured a grid of columns with Phase Dates present in each column where a Phase value has been set. For every Phase Date found in that grid, we run our checks and report when issues are encountered. This works very well, except for the failure of the Overlapping Phase Date checks.
If there is a better way to structure the checks (there's around 200 of them), then I am certainly open to suggestions, but as you can see, even explaining the structure is difficult.

What I have tried to detail in this request is as close as possible to the scenario that we are encountering in our actual system, with respect to the Overlapping Dates process, but simplified as much as possible. In reality, there are more Phases and we check both Start and End Dates as well.

Regarding the fail text and column names being similar, I just did that to simplify the explaination for my generic data only. In reality, the actual messages are way more expansive and provide clear indication to the end-users exactly what each error validation issue has been encountered.

Hope this helps.

12

Hi @Ifor

Oh, I was so focused in responding to your questions that I missed that you had included code just below those questions. Sorry about that,

I tried the code in my system leveraging the generated Table Data that is used in my question and this looks very close to what we need to resolve our overlapping dates error validation process. I will now try leveraging the code with our real data and provide feedback, once I have completed that integration process.

Thanks so much to taking the time to provide this feedback. It is very much appreciated!!! I certainly have a whole lot more to learn to become even average with SQL Queries.

13

Hi @Ifor

A quick note to let you know that the sample code which you provide starting with "WITH MaxDatesSoFar", worked perfectly for me, once I got things adjusted in my target data set and code.

As important, I was able to review the output of each session of the code and improve my admittedly limited understanding of such SQL code. I now understand how the "OVER (PARTITION BY REC_ID ORDER BY PHASE_NAME)" portion of the code structures the dates so that the are comparible by row.

I also understand the importance of sorting the rows correctly, as my actual data contained "Pre-Phase" records that needed checking and that caused the rows to be out of order. But upon sorting the rows correctly, all worked perfectly.

Having struggled with this for some time, I very much appreciate you proviing the solution to my overlapping dates check.

Thanks so much for taking the time to assist me with this process. I it veru much appreciated!!!

Cheers,

Wayne