CODEWITHSUNDEEP
sqlt-sqldatetime
Future44
Future44

Reputation: 11

SQL - Grab Rows That Are Between 2 Dates Using Different Tables

I have 2 tables:

Table 1: 

Name, StartDate, EndDate

Ex: 

Timmy, 9/12/17 08:00:00, 9/13/17 00:00:00   
Timmy, 9/13/17 05:00:00, 9/13/17 07:00:00

Table 2: 

Name, StartHour, Data...

Ex: 

Timmy, 9/13/17 06:00:00, Data1...   
Timmy, 9/13/17 04:00:00, Data2...   
Timmy, 9/13/17 07:00:00, Data3...  
Timmy, 9/12/17 14:00:00, Data4...

So, I need to get every record in Table 2 where that start hour is NOT inside of a date range in Table 1. This needs to be done for every name (so there can be multiple matching names in T1/T2 (see T2's data columns are different). It should be inclusive on the left side, exclusive on the right side.

So for this query, I want to see 

Timmy, 9/13/17 04:00:00, Data2...  
Timmy, 9/13/17 07:00:00, Data3... (Inclusive on end date)

I don't mind if its joined; I can just remove extra columns. I don't want to see duplicates though. I also keep hitting problems where I'll check if NOT (StartHour >= StartDate AND StartHour < EndDate). So each row/startHour in Table 2 needs to be checked against every row with a matching name in Table 1. I can't deal with that; I think it needs a subquery, but I don't know.

Checking if it is inside the ranges, but then negate because I don't want them if they are inside the range (inclusive,exclusive).

Upvotes: 0

Views: 73

Answers (2)

Soukai
Soukai

Reputation: 463

As suggested above, NOT EXISTS is probably the way to go here. NOT IN is another option. The idea being that you base the query on Table 2, then filter the output in the WHERE clause using NOT EXISTS against Table 1.

For example:

SELECT
    t2.Name,
    t2.StartHour
FROM #Table2 t2
WHERE NOT EXISTS (
        SELECT 1
        FROM #Table1 t1
        WHERE t1.Name = t2.Name
            AND t1.StartDate <= t2.StartHour
            AND t2.EndDate > t2.StartHour
    );

More information on EXISTS here, as well as a comparison between EXISTS and IN:

EXISTS (Transact-SQL)

Upvotes: 0

digital.aaron
digital.aaron

Reputation: 5707

Given the following tables:

CREATE TABLE #table1 ([Name] VARCHAR(15), startdate DATETIME, enddate DATETIME)
CREATE TABLE #table2 ([Name] VARCHAR(15), starthour DATETIME, [data] VARCHAR(MAX))

INSERT INTO #table1 (Name, startdate, enddate)
VALUES ('Timmy','9/12/17 08:00:00','9/13/17 00:00:00'),
('Timmy','9/13/17 05:00:00','9/13/17 07:00:00')

INSERT INTO #table2 (Name, starthour, data)
VALUES ('Timmy','9/13/17 06:00:00','Data1...'),
('Timmy','9/13/17 04:00:00','Data2...'),
('Timmy','9/13/17 07:00:00','Data3...'),
('Timmy','9/12/17 14:00:00','Data4...')

The following query seems to work for what you want:

SELECT t2.name, t2.starthour, t2.[data] 
FROM #table2 t2
INNER JOIN #table1 t1 ON t1.name = t2.name
WHERE t2.starthour BETWEEN t1.startdate AND t1.enddate

Results:

[name]  [starthour]             [Data]
----------------------------------------
Timmy   2017-09-13 06:00:00.000 Data1...
Timmy   2017-09-13 07:00:00.000 Data3...
Timmy   2017-09-12 14:00:00.000 Data4...

Upvotes: 1

Related Questions