php - fetch from two tables with a single query having multiple time range -
i have 2 table report , report1
report table
id user dt 1 2014-06-23 05:30:00 2 b 2014-06-23 06:30:00 3 c 2014-06-23 06:31:00 4 d 2014-06-23 07:30:00 to generate study multiple date range did
range count 06:31:00-07:30:00 2 5:30:00-6:30:00 2 file.php
$query=mysql_query("select case when date_format(dt,'%h:%i:%s') between '05:30:00' , '06:30:00' , date_format(dt,'%y:%m:%d')=date(curdate()) '5:30:00-6:30:00' when date_format(dt,'%h:%i:%s') between '06:31:00' , '07:30:00' , date_format(dt,'%y:%m:%d')=date(curdate()) '06:31:00-07:30:00' when date_format(dt,'%h:%i:%s') between '07:31:00' , '08:30:00' , date_format(dt,'%y:%m:%d')=date(curdate()) '07:30:00-08:30:00' else null end timerange, count(*) number study grouping timerange having timerange not null"); echo'<html> <head> <title>count user info timewise</title> </head> <h1>count user</h1> <table border="1" cellspacing="1"> <tr> <th>range</th> <th>count</th> <th>apps count</th>'; while($row = mysql_fetch_array($query)) { echo "<tr>"; echo "<td>" . $row['timerange'] . "</td>"; echo "<td>" . $row['number'] . "</td>"; echo "</tr>"; } echo "</table>"; echo "</html>"; ?> now want generate study adding 1 more column fetch table report1
range count countapps 06:31:00-07:30:00 2 count report1 5:30:00-6:30:00 2 count report1 report1 table
user dt 2014-06-26 05:30:00 b 2014-06-26 06:00:00 how can in single query
i think utilize current query sub query , bring together against other table (as current query generates timerange name other table keyed on).
something this, although need add together date sub query (not sure date want bring together on - records same date?):-
select sub0.timerange, sub0.number, report1.`count` ( select case when date_format(dt,'%h:%i:%s') between '05:30:00' , '06:30:00' '5:30:00-6:30:00' when date_format(dt,'%h:%i:%s') between '06:31:00' , '07:30:00' '06:31:00-07:30:00' when date_format(dt,'%h:%i:%s') between '07:31:00' , '08:30:00' '07:30:00-08:30:00' else null end timerange, count(*) number study grouping timerange having timerange not null ) sub0 left outer bring together report1 on report1.range = sub0.timerange edit - bit clunky, think it:-
select sub0.timerange, sub0.number, count(*) countapps ( select case when date_format(dt,'%h:%i:%s') between '05:30:00' , '06:30:00' str_to_date(concat(curdate(), ' ', '05:30:00'), '%y-%m-%d %h:%i:%s') when date_format(dt,'%h:%i:%s') between '06:31:00' , '07:30:00' str_to_date(concat(curdate(), ' ', '06:31:00'), '%y-%m-%d %h:%i:%s') when date_format(dt,'%h:%i:%s') between '07:31:00' , '08:30:00' str_to_date(concat(curdate(), ' ', '07:30:00'), '%y-%m-%d %h:%i:%s') else null end startrange, case when date_format(dt,'%h:%i:%s') between '05:30:00' , '06:30:00' str_to_date(concat(curdate(), ' ', '06:30:00'), '%y-%m-%d %h:%i:%s') when date_format(dt,'%h:%i:%s') between '06:31:00' , '07:30:00' str_to_date(concat(curdate(), ' ', '07:30:00'), '%y-%m-%d %h:%i:%s') when date_format(dt,'%h:%i:%s') between '07:31:00' , '08:30:00' str_to_date(concat(curdate(), ' ', '08:30:00'), '%y-%m-%d %h:%i:%s') else null end endrange, case when date_format(dt,'%h:%i:%s') between '05:30:00' , '06:30:00' '05:30:00-06:30:00' when date_format(dt,'%h:%i:%s') between '06:31:00' , '07:30:00' '06:31:00-07:30:00' when date_format(dt,'%h:%i:%s') between '07:31:00' , '08:30:00' '07:30:00-08:30:00' else null end timerange, count(*) number study date_format(dt,'%y:%m:%d')=date(curdate()) grouping startrange, endrange, timerange having timerange not null ) sub0 left outer bring together report1 on report1.dt between sub0.startrange , sub0.endrange grouping sub0.timerange, sub0.number php mysql
No comments:
Post a Comment