CODEWITHSUNDEEP
sqlsql-servert-sql
d.harvey
d.harvey

Reputation: 13

Get a start and end date from a table with a single date grouping by id and status type

I'm trying to create a query that for an ID gets the start date and end date based on the ID and Status.

I only have the three columns to work with, there are no missing dates. Statuses can go back and forth e.g. from Green to Yellow and back to Green. There is also only one status per day per ID.

The base table looks like this

tblOne

ID  |StartDate      |Status |
100 |1/01/2020      |green  |
100 |2/01/2020      |yellow |
100 |3/01/2020      |yellow |
200 |3/01/2020      |green  |
100 |4/01/2020      |Orange |
200 |4/01/2020      |Orange |
100 |5/01/2020      |Orange |
200 |5/01/2020      |Red    |
100 |6/01/2020      |Orange |
100 |7/01/2020      |yellow |
100 |8/01/2020      |Orange |
100 |9/01/2020      |Orange |
100 |10/01/2020     |Orange |
100 |11/01/2020     |Orange |
100 |12/01/2020     |Red    |

The table I want to create will look like this

tblTwo

ID  |StartDate  |EndDate    |Status |
100 |1/01/2020  |1/01/2020  |Green  |
100 |2/01/2020  |3/01/2020  |yellow |
100 |4/01/2020  |6/01/2020  |Orange |
100 |7/01/2020  |7/01/2020  |yellow |
100 |8/01/2020  |11/01/2020 |Orange |
100 |12/01/2020 |12/01/2020 |Red    |
200 |3/01/2020  |3/01/2020  |green  |
200 |4/01/2020  |4/01/2020  |Orange |
200 |5/01/2020  |5/01/2020  |Red    |

Code to create tblOne

CREATE TABLE tbleOne(ID int, StartDate Date, Status VARCHAR(20))
INSERT INTO tblOne VALUES
(100,'2020-01-01','green'),
(100,'2020-01-02','yellow'),
(100,'2020-01-03','yellow'),
(200,'2020-01-03','green'),
(100,'2020-01-04','Orange'),
(200,'2020-01-05','Orange'),
(100,'2020-01-05','Orange'),
(200,'2020-01-06','Red'),
(100,'2020-01-06','Orange'),
(100,'2020-01-07','yellow'),
(100,'2020-01-08','Orange'),
(100,'2020-01-09','Orange'),
(100,'2020-01-10','Orange'),
(100,'2020-01-11','Orange'),
(100,'2020-01-12','Red')

Upvotes: 1

Views: 281

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is an example of a gaps-and-islands problem. You can subtract a sequence of numbers from the date to get a constant that can be used for aggregation:

select id, status, min(startdate), max(startdate)
from (select t.*, row_number() over (partition by id, status order by startdate) as seqnum
      from tblone t
     ) t
group by id, status, dateadd(day, - seqnum, startdate)
order by id, min(startdate);

Here is a db<>fiddle.

Upvotes: 2

Related Questions