How to find two consecutive rows sorted by date, containing a specific value?

user_1732660 0
Viewed 587

I have a table with the following structure and data in it:

| ID    | Date          | Result    |
|----   |------------   |--------   |
| 1     | 30/04/2020    | +         |
| 1     | 01/05/2020    | -         |
| 1     | 05/05/2020    | -         |
| 2     | 03/05/2020    | -         |
| 2     | 04/05/2020    | +         |
| 2     | 05/05/2020    | -         |
| 2     | 06/05/2020    | -         |
| 3     | 01/05/2020    | -         |
| 3     | 02/05/2020    | -         |
| 3     | 03/05/2020    | -         |
| 3     | 04/05/2020    | -         |

I'm trying to write an SQL query (I'm using SQL Server) which returns the date of the first two consecutive negative results for a given ID. For example, for ID no. 1, the first two consecutive negative results are on 01/05 and 05/05. The first two consecutive results for ID No. 2 are on 05/05 and 06/05. The first two consecutive negative results for ID No. 3 are on on 01/05 and 02/05 .

So the query should produce the following result: 

| ID    | FirstNegativeDate     |
|----   |-------------------    |
| 1     | 01/05                 |
| 2     | 05/05                 |
| 3     | 01/05                 |

Please note that the dates aren't necessarily one day apart. Sometimes, two consecutive negative tests may be several days apart. But they should still be considered as "consecutive negative tests". In other words, two negative tests are not 'consecutive' only if there is a positive test result in between them.

How can this be done in SQL? I've done some reading and it looks like maybe the PARTITION BY statement is required but I'm not sure how it works.

gaps-and-islands window-functions date sql-server sql
2 Answers

This is a gaps-and-island problem, where you want the start of the first island of '-'s that contains at least two rows.

I would recommend lead() and aggregation:

select id, min(date) first_negative_date
from (
    select t.*, lead(result) over(partition by id order by date) lead_result
    from mytable t
) t
where result = '-' and lead_result = '-'
group by id
user_106767160
user_134165500
Related