CODEWITHSUNDEEP
mysqlsqltimemaxmin
asolole87
asolole87

Reputation: 21

how to Get max time and min time in one field?

I want to ask about mysql query

I have table data like this

My data

`userid | time| date
609  | 07:56:31|2014-01-23 
609  | 21:20:47|2014-01-23
609  | 17:25:27|2014-01-24
609  | 17:25:29|2014-01-24
609  | 17:13:54|2014-01-27
609  | 17:13:56|2014-01-27
609  | 07:55:09|2014-01-27`

I've tried various ways

but have not been successful

like 

set @start='1900-01-01';
set @finish='2033-12-31';
SELECT data.userid,t.clock_out,data.date from data 
inner join (select max(time)as clock_out,date,userid from data where time>='10:01' group by data.userid,tgl)t
on(data.date = t.date and data.userid=t.userid and t.clock_out=date.time)
rule
clock_in where time<='10:00' and clock_out time>'10:00',clock_in or clock_out can fill in the blank/empty,,the date for example 25 and 26 is not input data,but can be retrieved from variable @start='1900-01-01'and @finish='2033-12-31';
and result would be 
userid|date |clock_in | clock_out
609  |2014-01-23 |07:56 |21:20
609  |2014-01-24 |      |17:25
609  |2014-01-25 |      |
609  |2014-01-26 |      |
609  |2014-01-27 |07:55 |17:13
.

Can someone help me....:( thanks.in advance....

Upvotes: 2

Views: 2190

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

I think this is what you want, it isn't exactly as your answer but I think this is correct.

You need to join in a list of all unique dates for that user id and then use that list to group by and get min() and max() from time.

select t1.userid, 
       t2.date, 
       min(t1.time) as in_time, 
       max(t1.time) as out_time
  from test t1
  join (select distinct date from test where userid = 609) t2
 where t1.date = t2.date
   and userid = 609
 group by t1.userid, t2.date;

I would also say that it seems far better to instead of a date and a time just store a datetime and then group by some function that selects the date-part.

http://sqlfiddle.com/#!2/7b8732/1

Upvotes: 1

Oli
Oli

Reputation: 2452

Have you tried GROUP_CONCAT?

SELECT user_id, date, GROUP_CONCAT(time) as in_out FROM data GROUP BY date ORDER BY date,time;

This isn't 100% what you need, but might be close enough?

Upvotes: 0

Related Questions