Jump to content


Photo

output first log-in and log- out of the day


  • Please log in to reply
1 reply to this topic

#1 massive

massive
  • Members
  • PipPipPip
  • Advanced Member
  • 40 posts

Posted 20 June 2006 - 01:56 AM

Hi,there I have an attendance report and employees logs in and logs out for that specific date and time. I want to get the employees first log-in of the day and the last log-out of the day from its timestamp but in a day..employees logs-in and logs-out quite often I tried to use DISTINCT but the timestamp is still unique so my reports generates like this lets say:


Employee1 08:02:11
Employee1 08:03:32
Employee1 08:10:23
Employee1 08:12:11
Employee1 08:20:11
Employee1 08:20:16
Employee2 08:02:11
Employee2 08:03:32
Employee2 08:10:23
Employee3 08:12:11
Employee3 08:20:11
Employee1 08:20:16

i only need this:

Employee1 08:02:11 in
Employee1 08:20:16 out
and so on...


heres the my code its a join query
$query  = "SELECT DISTINCT concat( users.lastname, ', ', users.firstname ) AS name, time_log.log,                time_log.ts
FROM users, time_log
WHERE time_log.user = users.userID
AND time_log.ts LIKE '2006-05-25 08:%'
ORDER BY ts ASC";

thanks in advance


#2 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 June 2006 - 08:18 AM

It may be possible with one query, but I'm no expert when it comes to MySQL queries, so I'd probably just run the following two queries:
First time:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT concat( users.lastname, ', ', users.firstname ) AS name, time_log.log, time_log.ts
FROM users, time_log
WHERE time_log.user = users.userID AND time_log.ts LIKE '2006[span style=\'color:orange\']-05-25 08:%'[/span]
ORDER BY ts ASC LIMIT 1 [!--sql2--][/div][!--sql3--]
Last time:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT concat( users.lastname, ', ', users.firstname ) AS name, time_log.log, time_log.ts
FROM users, time_log
WHERE time_log.user = users.userID AND time_log.ts LIKE '2006[span style=\'color:orange\']-05-25 08:%'[/span]
ORDER BY ts DESC LIMIT 1 [!--sql2--][/div][!--sql3--]
Someone with a lot more MySQL knowledge might come along soon and show you an easier way though!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users