如何从此表中选择日期“span”？

Question

I have a SELECT result that contain columns with date. This is some data from the result:

ID   Name    status         start_date      end_date
A1   John    Planning       2017-03-03      2017-03-05
A1   John    Realizing      2017-03-05      2017-04-05
A1   John    Finishing      2017-04-05      2017-04-15
A1   John    Launching      2017-04-15      2017-04-16
A2   Lisa    Planning       2017-03-09      2017-03-13
A2   Lisa    Realizing      2017-03-13         NULL

I'd like to know the progress status from planning -> Realizing -> Finishing -> Launching for each ID.

I've used this query but I don't know if it will be reliable and fast enough. The real table contain thousand of values.

SELECT x.id, x.name, CONCAT_WS(' to ', MIN(x.start_date), MAX(x.end_date)) AS progress FROM (SELECT * FROM team_project WHERE ID = 'A1')x

Expected output :

Name             Progress               total
John     2017-03-03 to 2017-04-16      44 days
Lisa     2017-03-09 to NOW               -

Answer 1

I think withgroup by you can like this

SELECT id, name, CONCAT_WS(' to ', MIN(start_date), MAX(end_date)) AS progress 
FROM team_project group by ID