Find the name, start date and duration in months of projects that have the earliest end date - mysql

I was studying subqueries in sql and couldn't figure out the answer to this question. Following are the tables.

Project(p͟r͟o͟j͟n͟o͟, projname, prestdate, prendate)

+--------+----------------------+------------+------------+
| projno | projname             | prstdate   | prendate   |
+--------+----------------------+------------+------------+
| AD3100 | Admin Services       | 2014-01-01 | 2015-02-01 |
| AD3110 | General AD Systems   | 2014-01-01 | 2015-02-01 |
| MA2113 | W L Prod Cont Progs  | 2014-02-15 | 2014-12-01 |
| PL2100 | Weld Line Planning   | 2014-01-01 | 2014-09-15 |

I came up with this but I think its wrong:

select projname
     , prstdate
     , MONTH(prendate - prstdate) as duration 
  from Proj 
 where prendate - prstdate IN (select MIN(prendate - prstdate) from Proj);

Solution

You are on the right track. But your query is looking at the duration of the projects, not the end date.

Second, the calculation of the duration is off. The best function to use is TIMESTAMPDIFF().

So, just modify the code to look at the end date instead of the duration:

select projnamek, prstdate
       timestampdiff(month, prstdate, prendate) as duration 
from Proj 
where prendate = (select min(prendate) from Proj);