August 3, 2017 at 9:54 am
Table A: Has a start date and an end date in it, for example, 01/01/2017 and 12/31/2017.
Table B: Also has date ranges which may or may not be subsets of the date range in the first table.
Table A has a one to many relationship with Table B.
Is there a way in SQL to ask the question: For each record in table A is the entire date range covered by the subsets in table B?
It is okay if the subsets in table B overlap each other as long as every date in the range in table A is also contained in table B.
August 3, 2017 at 10:00 am
Without any sample data and expected result, I'm strugging to really visualise what your asking here. In simple terms, yes, you can join on dates. Depending on the date(time)s you have stored. YOu could do something like this:SELECT *
FROM TableA A
JOIN TableB B ON A.StartDate <= B.StartDate AND A.EndDate >= B.EndDate;
Edit: Wrong alias.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2017 at 10:09 am
Table A Data:
Id: 1, Name: Ted, Start Date: 01/01/2017, End Date: 12/31/2017
Table B Data:
Id: 1, Date1: 01/01/2017, Date2: 01/15/2017
Id: 1, Date1: 02/01/2017, Date2: 02/03/2017
Id: 1, Date1: 01/01/2017, Date2: 06/15/2017
Id: 1, Date1: 03/01/2017, Date2: 03/15/2017
Id: 1, Date1: 11/01/2017, Date2: 11/15/2017
Id: 1, Date1: 12/01/2018, Date2: 12/15/2018
How do I ask the question: Is each date in the date range in table A also found at least once in a date range in table B. It is possible that for some records in table A that each date in the range will be found in the related table B but it is also possible that each date in the range in table A isn't found in one of the ranges in table B for that record.
August 3, 2017 at 10:13 am
thelenj - Thursday, August 3, 2017 10:09 AMTable A Data:
Id: 1, Name: Ted, Start Date: 01/01/2017, End Date: 12/31/2017Table B Data:
Id: 1, Date1: 01/01/2017, Date2: 01/15/2017
Id: 1, Date1: 02/01/2017, Date2: 02/03/2017
Id: 1, Date1: 01/01/2017, Date2: 06/15/2017
Id: 1, Date1: 03/01/2017, Date2: 03/15/2017
Id: 1, Date1: 11/01/2017, Date2: 11/15/2017
Id: 1, Date1: 12/01/2018, Date2: 12/15/2018How do I ask the question: Is each date in the date range in table A also found at least once in a date range in table B. It is possible that for some records in table A that each date in the range will be found in the related table B but it is also possible that each date in the range in table A isn't found in one of the ranges in table B for that record.
What would be your expected resultset?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2017 at 11:32 am
For the example the expected result set could be as simple as 'false', that is, each date in the date range for the 1st record in Table A cannot be found within the date ranges for the related records in Table B.
August 3, 2017 at 12:32 pm
The main issue is that the intervals in your second table aren't packed. Here is an article that discusses Packing Intervals.
The approach I took was to pack the intervals in your second table and then used that to determine if the intervals in the first table occurred during[1] any of the packed intervals.
First, it is best to set up sample data as below. This makes it easy for others to quickly get started, so it encourages others to contribute.
/* Sample Data Setup */
CREATE TABLE #A (
Id TINYINT,
Name VARCHAR(25),
Start_Date DATE,
End_Date DATE
)
INSERT #A
VALUES (1, 'Ted', '01/01/2017', '12/31/2017')
CREATE TABLE #B (
Id TINYINT,
Date1 DATE,
Date2 DATE
)
INSERT #B
VALUES
(1, '01/01/2017', '01/15/2017')
, (1, '02/01/2017', '02/03/2017')
, (1, '01/01/2017', '06/15/2017')
, (1, '03/01/2017', '03/15/2017')
, (1, '11/01/2017', '11/15/2017')
, (1, '12/01/2018', '12/15/2018')
/* Suggested Solution */
SELECT *
FROM #A a
INNER JOIN #B b
ON a.ID = b.ID
;
WITH C1 AS (
SELECT *,
MAX(Date2) OVER(
PARTITION BY b.Id
ORDER BY Date1, Date2, b.ID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) prev_end_dt
FROM #B b
), C2 AS (
SELECT *,
SUM(is_start) OVER(
PARTITION BY C1.Id
ORDER BY C1.Date1, C1.Date2
ROWS UNBOUNDED PRECEDING ) interval_grp
FROM C1
CROSS APPLY ( VALUES( CASE WHEN Date1 <= prev_end_dt THEN NULL ELSE 1 END ) ) i(is_start)
), C3 AS (
SELECT ID, MIN(Date1) AS Start_Date, MIN(Date2) AS End_Date
FROM C2
GROUP BY ID, interval_grp
)
SELECT *,
CASE
WHEN EXISTS
(
SELECT 1
FROM C3
WHERE a.Start_Date >= C3.Start_Date
AND a.End_Date <= C3.End_Date
)
THEN 'True'
ELSE 'False'
END
FROM #A aDROP TABLE #A, #B
-------------------------
1) James F. Allen (no known relation) developed nomenclature for discussing interval relationships.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2017 at 12:39 pm
Thank you, I'll be spending the rest of the afternoon studying this solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply