Eli G Eli G - 5 months ago 7
MySQL Question

SQL get the min/max time per date in one row

i need a help here, im trying to build a query which returns the max and min time per date in one row for example i have this table:

+---------+------------------------+----------+
|name |Dates |Door |
+---------+------------------------+----------+
|Maria |2012-02-14 09:04:45.397 |Garage |
|Maria |2012-02-14 12:14:20.997 |Entrance |
|Maria |2013-02-14 12:20:59.407 |Exit |
|Maria |2012-02-13 12:24:20.997 |garage |
|Eli |2013-02-13 10:30:59.407 |Entrance |
|Eli |2013-02-13 12:30:59.407 |Exit |
+---------+------------------------+----------+


the results should be like :



+---------+------------------------+-----------------------------+
|name |Entrance |Exit |
+---------+------------------------+-----------------------------+
|Maria |2012-02-14 09:04:45.397 |2013-02-14 12:20:59.407 |
|Maria |2012-02-13 12:14:20.997 | null |
|Eli |2013-02-13 10:30:59.407 |2013-02-13 12:30:59.407 |
+---------+------------------------+-----------------------------+





any help would appreciate :)

Answer

you can try like this

select name, min(Dates) as entrance, max(dates) as exit from  your table 

group by cast(Dates as varchar(10)),name

or

select name, min(Dates) as entrance, max(dates) as exit from  your table 

group by Date(Dates),name