MS SQL Server Identify Overlapping Dates

Transact-SQL
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