CODEWITHSUNDEEP
sqlsql-server
MikeV
MikeV

Reputation: 51

Query to get count of Rows within a date range stored in records

I'm trying to get the number of records that have the same FKID and are in the same range of Dates (between Start and End) For Example: FK ID has 2 rows that are within the given date range: ID 210 is between 10:15-11:32 and ID 213 is between 10:30 and 11:47 (they are also on the same day). I need a query that will tell me which IDs have the same FKID and are within the same range. I've been working at this for a while but can't figure it out! Thanks!

ID FKID Start End TotalTime
208 40 2021-07-29 09:30:00.000 2021-07-29 09:30:00.000 0.9
209 37 2021-07-29 10:00:00.000 2021-07-29 10:00:00.000 0.9
210 39 2021-07-29 10:15:00.000 2021-07-29 11:32:00.000 77.76
211 40 2021-07-29 13:00:00.000 2021-07-29 14:17:00.000 77.76
212 34 2021-07-29 13:00:00.000 2021-07-29 14:28:00.000 88.8685714285714
213 39 2021-07-29 10:30:00.000 2021-07-29 11:47:00.000 77.76

Upvotes: 3

Views: 792

Answers (2)

GarethD
GarethD

Reputation: 69819

We first need to do a self join to get all the rows in the table with the Same FKID:

SELECT  *
FROM    T AS t1
        INNER JOIN T AS t2
            ON t2.FKID = t1.FKID -- SAME FKID
            AND t2.ID <> t1.ID -- Different ID;

The condition t2.ID <> t1.ID will ensure you aren't joining the same row to itself, instead looking for other rows in the same table. This will return all combinations of rows, e.g.

ID1 ID2
210 213
213 210

If however you only want each tuple once, you can change to t2 > t1.ID which will return only

ID1 ID2
210 213

Then for overlapping ranges, the verbose version of the logic is:

  • Start date of 2nd Row is after Start of first row and before and end date of 1st Row OR
  • End date of 2nd Row is after Start of 1st Row and before the end date of 1st Row

So in SQL terms this becomes:

CREATE TABLE #T (ID INT, FKID INT, Start DATETIME, [End] DATETIME, TotalTime INT);

INSERT INTO #T(ID, FKID, Start, [End], TotalTime)
VALUES
    (208, 40, '2021-07-29 09:30:00', '2021-07-29 09:30:00', 0.9),
    (209, 37, '2021-07-29 10:00:00', '2021-07-29 10:00:00', 0.9),
    (210, 39, '2021-07-29 10:15:00', '2021-07-29 11:32:00', 77.76),
    (211, 40, '2021-07-29 13:00:00', '2021-07-29 14:17:00', 77.76),
    (212, 34, '2021-07-29 13:00:00', '2021-07-29 14:28:00', 88.8685714285714),
    (213, 39, '2021-07-29 10:30:00', '2021-07-29 11:47:00', 77.76);

SELECT  t1.*
FROM    #T AS t1
        INNER JOIN #T AS t2
            ON t2.FKID = t1.FKID -- SAME FKID
            AND t2.ID <> t1.ID -- Different ID 
WHERE   (t2.Start > t1.Start AND t2.Start < t1.[End])
OR      (t2.[End] > t1.Start AND t2.[End] < t1.[End]);

We can however simplify this with De Morgan's laws to simply:

SELECT  t1.*
FROM    #T AS t1
        INNER JOIN #T AS t2
            ON t2.FKID = t1.FKID -- SAME FKID
            AND t2.ID <> t1.ID -- Different ID
WHERE   t2.Start < t1.[End]
AND     t2.[End] > t1.Start;

In both cases the output is:

ID FKID Start End TotalTime
210 39 2021-07-29 10:15:00.000 2021-07-29 11:32:00.000 77.76
213 39 2021-07-29 10:30:00.000 2021-07-29 11:47:00.000 77.76

Example on db<>fiddle

If you want to include rows where the start date equals the end date of a previous row, then just change < to <= and > to >=:

SELECT  t1.*
FROM    #T AS t1
        INNER JOIN #T AS t2
            ON t2.FKID = t1.FKID -- SAME FKID
            AND t2.ID <> t1.ID -- Different ID
WHERE   t2.Start <= t1.[End]
AND     t2.[End] >= t1.Start;

Example on db<>fiddle note rows with ID 214 and 215

Upvotes: 3

Marc Guillot
Marc Guillot

Reputation: 6465

You can JOIN the table with itself, looking for records of the same FKID whose ranges overlap (StartA <= EndB) and (EndA >= StartB)

This returns the overlaping IDs :

select A.ID as ID1, B.ID as ID2
from MyTable as A
     inner join MyTable as B on A.FKID = B.FKID and 
                                A.Start <= B.End and 
                                A.End >= B.Start

While this query just returns the number of overlaping rows.

select count(*) as Overlaps
from MyTable as A
     inner join MyTable as B on A.FKID = B.FKID and 
                                A.Start <= B.End and 
                                A.End >= B.Start

Upvotes: 0

Related Questions