php - Mysql joins 3 tables with multiple fields -

-

i trying join employee table "employees", timeclock transaction table "transactions" , table "hta" time field. want timeclock table add time employee worked between set of days. want other table "hta" add time each employee between set of days , employee table employees name. make things simple right working 1 day. therefore there 1 entry per employee. no 1 has clocked in multiple times day. in hta table there multiple entries per person day.

here code

select time_format( sec_to_time( sum( time_to_sec( `t`.`hours_today` ) ) ) , "%h:%i:%s" )  `hours_today` ,  time_format( sec_to_time( sum( time_to_sec( `h`.`run_time` ) ) ) , "%h:%i:%s" )  `run_time` ,  `t`.`employee_id` , `e`.`employee_id` , `e`.`displayname`  `transactions` t left join `hta` h  on `t`.`employee_id` = `h`.`employee_id`  , `t`.`date` = `h`.`date`  left join `employees` e on `t`.`employee_id` = `e`.`employee_id`  `t`.`date` >= "2015-08-14"  , `t`.`date` <= "2015-08-14"  group `t`.`employee_id`  order `t`.`employee_id` asc

the output getting hours_today wrong, run_time correct, employee_id correct, , display name correct.
on employee 1 hours_today should 05:48:00. remember employee has 1 entry in hours_today column date range. mysql output employee 29:00:00. looks if date timeclock table not being limited hta table. employee 2 hours_today should 09:34:00 output query 95:40:00

i have tried more detailed code such 

select time_format(sec_to_time(sum(time_to_sec(t.hours_today))), "%h:%i:%s") hours_today, t.employee_id, e.employee_id, e.displayname transactions t  left join employees e on t.employee_id = e.employee_id left join (select employee_id,  time_format(sec_to_time(sum(time_to_sec(run_time))), "%h:%i:%s") run_time hta  date(date_time) >= "2015-08-14" , date(date_time) <= "2015-08-14" group employee_id) hta  on e.employee_id = hta.employee_id left join hta h on hta.employee_id = h.employee_id  date >= "2015-08-14" , date <= "2015-08-14" group t.employee_id order t.employee_id asc

and 

select time_format(sec_to_time(sum(time_to_sec(t.hours_today))), "%h:%i:%s") hours_today, time_format(sec_to_time(sum(time_to_sec(h.run_time))), "%h:%i:%s")  run_time, t.employee_id, e.employee_id, e.displayname transactions t left join  employees e on t.employee_id = e.employee_id left join hta h on e.employee_id = h.employee_id , t.date = date(h.date_time) date >= "2015-08-14" , date <= "2015-08-14"  group t.employee_id order t.employee_id asc

after frustration….

select `t1`.`displayname`, `t1`.`employee_id`, `t2`.`hours_today`, `t3`.`run_times` (select `employee_id`, `displayname`, `rate` `employees`) t1 left join (select time_format( sec_to_time( sum( time_to_sec(`hours_today`))) ,  "%h:%i:%s" ) hours_today, `employee_id`  `transactions` `date` = "2015-08-14" group `employee_id`) t2 on `t1`.`employee_id` = `t2`.`employee_id`  left join (select time_format( sec_to_time( sum( time_to_sec(`run_time`))) ,  "%h:%i:%s" ) run_times, `employee_id`  `hta` `date` = "2015-08-14" group `employee_id`) t3 on `t1`.`employee_id` = `t3`.`employee_id`

Comments

Post a Comment

Popular posts from this blog

dns - How To Use Custom Nameserver On Free Cloudflare? -

-
i have problem. i own small hosting company. trying add cloudflare site free cdn. problem when sign have change nameservers nameservers. have nameserver dns set through godaddy, how add them cloudflare? main site uses company's nameservers whenever add them site goes offline. when go cloudflare's dns records click on dropdown, click ns, , there's 2 boxes 'name' , 'nameserver'. mean input 'ns1' 'name' , 'ns1.domain.com' 'nameserver'? input server's ip addresses? is there way of accomplishing free version of cloudflare? that impossible. option have custom name servers available on business , enterprise. see https://www.cloudflare.com/plans . also, might mistaken. must change name servers in domain register settings, not on cloudflare.
Read more

python - Pygame screen.blit not working -

-
in program creating in python/pygame, screen.blit not working. in other programs have made, screen.blit works. error: traceback (most recent call last): file "c:...\main.py", line 46, in <module> screen.blit(sight.image, sight.rect) attributeerror: 'int' object has no attribute 'blit' the error in question caused screen variable being allocated integer instead of pygame.surface() object.perhaps accidentally rewrote variable somewhere? from can make of doing, seems stating position of blit full rect instead of coordinate. when do: screen.blit(sight.image,sight.rect) what program seeing is: screen.blit(sight.image,(sight.rect.x,sight.rect.y,sight.rect.width,sight.rect.height)) i assuming don't intend this. remember rect object consists of position, width , height. to correct code, put (sight.rect.x,sight.rect.y) location goes instead this: screen.blit(sight.image,(sight.rect.x,sight.rect.y)) the .x , .y corres
Read more

c# - Web API response xml language -

-
i having simple post url clients send post request. http://mydomain.com/requests/request post data: <request> <orderid>1e008921</orderid> <ordername>mc1</ordername> </request> to respond sending them xml <srequest> <requestedby>client 1 </requestedby> <requestname>test name</requestname> <requeststatus>success</requeststatus> <srequest> now client wants above xml in different language (say french ) how handle ? how send response language preference in web api ? specially post scenario ? (append language string ? or best practices ?) i suggest either include in url: http://mydomain.com/fr/requests/request or use http header accept-language . read more in answer to: good way changing language resources dynamically request
Read more