CODEWITHSUNDEEP
sqlsql-server
Yuval Haran
Yuval Haran

Reputation: 119

Sql checking if a date from 1 table is between 2 dates from different table

i have 2 sql tables

  1. has Id, date, boolean value
  2. has Id, date

so table 1 looks like this 

    1 3/1/2017 false
    2 3/1/2017 true
    1 1/1/2017 false
    2 10/12/2016 false

table 2 like this

    1 3/1/2017
    2 3/1/2017
    1 2/1/2017
    1 12/12/2016

The result I want is for each pair of dates in table 1 that have the same id and are following each other for example for id 1 it is 1/1/2017 and 3/1/2017 to find if there is a date in table 2 with the same id that is between those dates (same day inclusive) and the boolean is false.

so for example the result in this case would be
for id1 2/1/2017, 3/1/2017

How can I do this?

Upvotes: 2

Views: 926

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

How many rows there can be in Table1 with the same ID? I assume 2, but the query will work reasonably even with 1 or 3+.

"are following each other" - any two dates will follow each other, unless they are the same, so the only real check below is for inequality.

"the boolean is false" - there are two rows, which can have different boolean values. I assume both of them have to be false. (false is 0, true is 1)

Sample data

This is how your sample data should be presented in your question. At least you should write dates in a way that we don't have to guess what is month and what is day.

DECLARE @Table1 TABLE (ID int, dt date, Flag bit);
INSERT INTO @Table1 (ID, dt, Flag) VALUES
(1, '2017-01-03', 'false'),
(2, '2017-01-03', 'true'),
(1, '2017-01-01', 'false'),
(2, '2016-12-10', 'false');

DECLARE @Table2 TABLE (ID int, dt date);
INSERT INTO @Table2 (ID, dt) VALUES
(1, '2017-01-03'),
(2, '2017-01-03'),
(1, '2017-01-02'),
(1, '2016-12-12');

Query

WITH
CTE
AS
(
    SELECT
        ID
        ,MIN(dt) AS StartDT
        ,MAX(dt) AS EndDT
        ,MAX(CAST(Flag AS int)) AS MaxFlag
    FROM @Table1 AS Table1
    GROUP BY ID
)
SELECT
    CTE.ID
    ,A.dt
FROM
    CTE
    CROSS APPLY
    (
        SELECT
            Table2.dt
        FROM @Table2 AS Table2
        WHERE
            Table2.ID = CTE.ID
            AND Table2.dt >= CTE.StartDT
            AND Table2.dt <= CTE.EndDT
    ) AS A
WHERE
    StartDT < EndDT -- "are following each other"
    AND MaxFlag = 0 -- "the boolean is false" for both IDs
;

Result

+----+------------+
| ID |     dt     |
+----+------------+
|  1 | 2017-01-02 |
|  1 | 2017-01-03 |
+----+------------+

Index on Table2 on (ID, dt) will help a lot.

Upvotes: 2

Related Questions