T-SQL: Selecting every instance of min and max values

Question

I have a table called #TimeAtHome. It includes an address, the date and a flag atHome to indicate if the person was at home that day. I need to capture the min and max date for every grouping the person is not at home (0) for each address.

Here is some sample code:

create table #TimeAtHome (
    [address] varchar(100),
    [date] date,
    [atHome] bit
)

insert into #TimeAtHome
values ('123 ABC Street', '2020-01-01', '1'),
       ('123 ABC Street', '2020-01-02', '1'),
       ('123 ABC Street', '2020-01-03', '0'),
       ('123 ABC Street', '2020-01-04', '0'),
       ('123 ABC Street', '2020-01-05', '0'),
       ('123 ABC Street', '2020-01-06', '0'),
       ('123 ABC Street', '2020-01-07', '1'),
       ('123 ABC Street', '2020-01-08', '0'),
       ('123 ABC Street', '2020-01-09', '0'),
       ('123 ABC Street', '2020-01-10', '1'),
       ('777 Hello Ct', '2020-01-01', '1'),
       ('777 Hello Ct', '2020-01-02', '1'),
       ('777 Hello Ct', '2020-01-03', '1'),
       ('777 Hello Ct', '2020-01-04', '0'),
       ('777 Hello Ct', '2020-01-05', '1'),
       ('777 Hello Ct', '2020-01-06', '1')

Here is my desired outcome:

Answer 1

HERE IS ANOTHER WAY

    SELECT
    *
FROM 
(
    SELECT
        *
        ,ROW_NUMBER() OVER(PARTITION BY address order by date) PrevAtHome_A
        ,ROW_NUMBER() OVER(PARTITION BY address order by date DESC) PrevAtHome_D
    from #TimeAtHome
    WHERE AtHome = 0
)A
WHERE PrevAtHome_A =1 OR PrevAtHome_D =1
ORDER BY [address], [date]

Answer 2

Yet another possibility:

create table #TimeAtHome (
    [address] varchar(100),
    [date] date,
    [atHome] bit
)

insert into #TimeAtHome
values ('123 ABC Street', '2020-01-01', '1'),
       ('123 ABC Street', '2020-01-02', '1'),
       ('123 ABC Street', '2020-01-03', '0'),
       ('123 ABC Street', '2020-01-04', '0'),
       ('123 ABC Street', '2020-01-05', '0'),
       ('123 ABC Street', '2020-01-06', '0'),
       ('123 ABC Street', '2020-01-07', '1'),
       ('123 ABC Street', '2020-01-08', '0'),
       ('123 ABC Street', '2020-01-09', '0'),
       ('123 ABC Street', '2020-01-10', '1'),
       ('777 Hello Ct', '2020-01-01', '1'),
       ('777 Hello Ct', '2020-01-02', '1'),
       ('777 Hello Ct', '2020-01-03', '1'),
       ('777 Hello Ct', '2020-01-04', '0'),
       ('777 Hello Ct', '2020-01-05', '1'),
       ('777 Hello Ct', '2020-01-06', '1')
   




SELECT dt.address,
       MIN(dt.Dt) AS minDate,
       MAX(dt.Dt) AS maxDate
FROM (
        SELECT address, 
               t.Date AS Dt,
               DATEDIFF(D, ROW_NUMBER() OVER(partition by t.address ORDER BY t.Date), 
t.Date) AS DtRange
        FROM #TimeAtHome t
        WHERE t.atHome = 0
    ) AS dt
GROUP BY dt.address, dt.DtRange
ORDER BY address, minDate;

Answer 3

I suppose I used a simpler solution as this seems like a gaps and islands problem. So I used the LAG() function to find where the islands start and end based on the AtHome flag. Then I used the SUM() function to create a group and aggregated the dates from there:

SELECT Address,Min(Date) minDate, Max(date) maxDate
FROM
(
    SELECT *, SUM(CASE WHEN AtHome <> PrevAtHome THEN 1 ELSE 0 END) OVER(PARTITION BY Address order by date) Grp
    FROM(
      SELECT *, LAG(ATHome,1,AtHome) OVER(PARTITION BY address order by date) PrevAtHome
      from #TimeAtHome
      ) T
) Final
WHERE Athome = 0
GROUP BY Address,Grp
ORDER BY Address

Answer 4

The query is like this, Cte1 is used to get a full view of data that would be used in the next step. Cte2 is used to find mindate, Cte3 is used to get maxDate, and Rank func is used to join at the end

;WITH cte1
AS
(
    SELECT *, 
        LEAD(date) OVER (PARTITION BY address ORDER BY date) AS nextDate, 
        LEAD(atHome) OVER (PARTITION BY address ORDER  BY date) AS NextAtHome 
    FROM #TimeAtHome
    --ORDER BY address, date
),
CTE2 AS
(
    SELECT 
        address, 
        cte1.nextDate AS minDate,
        ROW_NUMBER() OVER (ORDER BY cte1.address , cte1.date) AS R1
    FROM cte1 
    WHERE cte1.atHome = 1 AND cte1.NextAtHome = 0
),
CTE3 AS
(
    SELECT 
        address, 
        date AS maxDate,
        ROW_NUMBER() OVER (ORDER BY cte1.address, cte1.date) AS R2
    FROM cte1 
    WHERE cte1.atHome = 0 AND cte1.NextAtHome = 1
)
SELECT CTE2.address,CTE2.minDate,CTE3.maxDate
FROM cte2
INNER JOIN cte3 ON cte2.R1 = Cte3.R2

Answer 5

We can try the following:

1. Get all minDate values joining the table with itself and checking if the current date the person is at home and not at home on the next date (will be subquery 1).
2. Get all maxDate the same way as in point 1 except checking that the person is back next date (subquery 2).
3. Match for each address first minDate with first maxDate, second minDate with second maxDate, and so on (join subquery 1 and 2).

SELECT q1.address,
    q1.minDate,
    q2.maxDate
FROM (
        SELECT ROW_NUMBER() OVER(
                PARTITION BY t2.address
                ORDER BY t2.date
            ) as row,
            t2.address,
            t2.date as minDate
        FROM #TimeAtHome t1 inner join #TimeAtHome t2 ON t1.address = t2.address and t1.date = DATEADD(DAY, -1, t2.date)
        WHERE t1.atHome = 1
            AND t2.atHome = 0
    ) q1
    INNER JOIN (
        SELECT ROW_NUMBER() OVER(
                PARTITION BY t1.address
                ORDER BY t1.date
            ) as row,
            t1.address,
            t1.date as maxDate
        FROM #TimeAtHome t1 INNER JOIN #TimeAtHome t2 ON t1.address = t2.address and t1.date = DATEADD(DAY, -1, t2.date)
        WHERE t1.atHome = 0
            AND t2.atHome = 1
    ) q2 ON q1.address = q2.address
    AND q1.row = q2.row

Please note the constraints for this query

1. Dates in the table are expected to be continuous so to find the next record in the table we can simply subtract a day t1.date = DATEADD(DAY, -1, t2.date).
2. The person starts at home so the first minDate when he goes out matches with the first maxDate when he is back.