Jump to content

Archived

This topic is now archived and is closed to further replies.

massive

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

Recommended Posts

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
[code]
$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";
[/code]

thanks in advance

Share this post


Link to post
Share on other sites
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] [color=green]DISTINCT[/color] concat( users.lastname, [color=red]', '[/color], users.firstname ) [color=green]AS[/color] name, time_log.log, time_log.ts
[color=green]FROM[/color] [color=orange]users,[/color] time_log
[color=green]WHERE[/color] time_log.user [color=orange]=[/color] users.userID [color=blue]AND[/color] time_log.ts [color=orange]LIKE[/color] [color=red]'2006[span style=\'color:orange\']-[/color]05[color=orange]-[/color]25 08:%'[/span]
[color=green]ORDER BY[/color] ts [color=green]ASC[/color] 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] [color=green]DISTINCT[/color] concat( users.lastname, [color=red]', '[/color], users.firstname ) [color=green]AS[/color] name, time_log.log, time_log.ts
[color=green]FROM[/color] [color=orange]users,[/color] time_log
[color=green]WHERE[/color] time_log.user [color=orange]=[/color] users.userID [color=blue]AND[/color] time_log.ts [color=orange]LIKE[/color] [color=red]'2006[span style=\'color:orange\']-[/color]05[color=orange]-[/color]25 08:%'[/span]
[color=green]ORDER BY[/color] ts [color=green]DESC[/color] LIMIT 1 [!--sql2--][/div][!--sql3--]
Someone with a lot more MySQL knowledge might come along soon and show you an easier way though!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.