Finding out mismatch between time lines.

Question

Finding out mismatch between time lines.

SSCrazy

Points: 2756

October 16, 2019 at 9:02 am

CREATE TABLE #Source
(
PlanId BIGINT,
StartDate DATE,
EndDate DATE
)
GO
CREATE TABLE #Destination
(
PlanId BIGINT,
StartDate DATE,
EndDate DATE
)
GO

INSERT INTO #Source
SELECT 1010,'01/01/2018','12/31/2018'
UNION ALL
SELECT 1010,'01/01/2019','12/31/2019'
UNION ALL
SELECT 1011,'01/01/2018','12/31/2018'
UNION ALL
SELECT 1011,'01/01/2019','12/31/2019'

INSERT INTO #Destination
SELECT 1010,'01/01/2018','12/31/2018'
UNION ALL
SELECT 1010,'01/01/2019','06/30/2019'
UNION ALL
SELECT 1010,'07/01/2019','12/31/2019'
UNION ALL
SELECT 1011,'01/01/2018','12/31/2018'
UNION ALL
SELECT 1011,'01/01/2019','05/31/2019'
UNION ALL
SELECT 1011,'07/01/2019','10/31/2019'
UNION ALL
SELECT 1011,'11/01/2019','12/31/2019'

SELECT * FROM #Source
SELECT * FROM #Destination

Hi, I have two tables, one is source and second one is destination. I'm trying to compare source and destination timeliness based on Plan ID, destination can have multiple date splits but it should match with source.

Ex: 1010 having 2 rows in source and 3 rows in destination but even though there is a gap in timeline values are accurate.

1011 there is a mismatch in timeline - missing 06/01/2019 to 06/31/2019, i would like to find out plan-id's which are having mismatch between source and destination. Hope it's clear.

This topic was modified 4 years, 6 months ago by koti.raavi. Reason: Missing key words

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

Pretty sure there will be a much elegant way to this - I came up with below. This will work if you a calendar table with all dates or you can create on temporary

SELECT s1.PlanId  ,
MIN(ISNULL (s1.calendardate , '22991231')) AS missingbegindate,
MAX(ISNULL(s1.calendardate , '19000101' ) ) AS missingenddate

FROM 
(
(
SELECT DISTINCT  planid , d.calendardate 
FROM #Source s
CROSS JOIN Datetable d 
WHERE d.calendardate BETWEEN s.StartDate AND enddate  ) s1
LEFT OUTER JOIN (
SELECT DISTINCT planid  , dd.calendardate  FROM #Destination d
CROSS JOIN Datetable dd 
WHERE dd.calendardate BETWEEN d.StartDate AND d.enddate  ) k2 

ON k2.PlanId = s1.PlanId
AND k2.calendardate = s1.calendardate ) 
WHERE k2.calendardate IS NULL 
GROUP BY s1.PlanId

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

Thanks for response, i have used function instead of table, trying to find out better version

CREATE FUNCTION DBO.Fn_spt_values(@StartDate DATE,@EndDate DATE)
RETURNS TABLE 
AS  
RETURN   
(
SELECT  DATENAME(YEAR, DATEADD(MONTH, x.number, @StartDate))  YEAR
       ,MONTH(DATEADD(MONTH, x.number, @StartDate)) AS MONTH
       ,DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)
)

SELECT * FROM 
(
SELECT * FROM #Source
CROSS APPLY DBO.Fn_spt_values(StartDate,EndDate)
) A
LEFT JOIN 
(
SELECT * FROM #Destination
CROSS APPLY DBO.Fn_spt_values(StartDate,EndDate)
) B
ON A.PlanId=B.PlanId AND A.YEAR=B.YEAR AND A.MONTH=B.MONTH
WHERE B.MONTH IS NULL

Neil Burton SSC-Insane Points: 22918 More actions · Answer 3

I did have a solution that worked without a calendar table but I spotted a mistake.

It's probably worth pointing out that there is no 31st of June.

This reply was modified 4 years, 6 months ago by Neil Burton. Reason: Mistake spotted

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

drew.allen SSC Guru Points: 76944 More actions · Answer 4

This may perform better. It's hard to tell with such a small sample size.

WITH data_dates AS
(
SELECT s.PlanId, dt.dt
FROM #Source AS s
CROSS APPLY (VALUES(s.StartDate), (DATEADD(DAY, 1, s.EndDate)) ) dt(dt)

UNION

SELECT d.PlanId, dt.dt
FROM #Destination AS d
CROSS APPLY (VALUES(d.StartDate), (DATEADD(DAY, 1, d.EndDate)) ) dt(dt)
)
, intervals AS
(
SELECT dd.PlanId, dd.dt AS StartDate, LEAD(dd.dt, 1) OVER(PARTITION BY dd.PlanId ORDER BY dd.dt) AS EndDate
FROM data_dates dd
)
SELECT i.PlanID, i.StartDate, DATEADD(DAY, -1, i.EndDate) AS EndDate
FROM intervals i
INNER JOIN #Source AS s
ON i.PlanId = s.PlanId
AND i.StartDate < s.EndDate
AND i.EndDate > s.StartDate
LEFT OUTER JOIN #Destination AS d
ON i.PlanId = d.PlanId
AND i.StartDate < d.EndDate
AND i.EndDate > d.StartDate
WHERE d.PlanId IS NULL;

To make this work, I did have to convert your closed intervals to half-open intervals (adding a day to the end date) and then convert back.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA