CODEWITHSUNDEEP
phpmysql
black
black

Reputation: 745

fetch from two tables with a single query having multiple time range

i have two table report and report1

report table

id   user    dt
1    a  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 a report like this with multiple date range i 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' and '06:30:00' and  DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
            THEN '5:30:00-6:30:00'
        WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '06:31:00' and '07:30:00' and DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
            THEN '06:31:00-07:30:00'
        WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '07:31:00' and '08:30:00' and DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
            THEN '07:30:00-08:30:00'
    ELSE NULL
    END AS TimeRange,
    COUNT(*) as number
FROM report
GROUP BY TimeRange
HAVING TimeRange IS 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 i want to generate the report adding one more column which will fetch from another table report1

range             count         countapps
06:31:00-07:30:00   2             count from report1
5:30:00-6:30:00     2             count from report1

report1 table

user    dt
a     2014-06-26 05:30:00
b     2014-06-26 06:00:00

how can i do this in a single query

Upvotes: 0

Views: 71

Answers (1)

Kickstart
Kickstart

Reputation: 21513

I think I would use your current query as a sub query and join that against the other table (as your current query generates the timerange name that the other table is keyed on).

Something like this, although you would need to add the date to the sub query as well (not sure what date you want to join on - are all records from the same date?):-

SELECT sub0.TimeRange, sub0.number, report1.`count`
FROM
(
    SELECT CASE
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '05:30:00' and '06:30:00'
                THEN '5:30:00-6:30:00'
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '06:31:00' and '07:30:00'
                THEN '06:31:00-07:30:00'
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '07:31:00' and '08:30:00'
                THEN '07:30:00-08:30:00'
            ELSE NULL
        END AS TimeRange,
        COUNT(*) as number
    FROM report
    GROUP BY TimeRange
    HAVING TimeRange IS NOT NULL
) sub0
LEFT OUTER JOIN report1
ON report1.range = sub0.TimeRange

EDIT - bit clunky, but think something like this would do it:-

SELECT sub0.TimeRange, sub0.number, COUNT(*) AS countapps
FROM
(
    SELECT 
        CASE
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '05:30:00' and '06:30:00'
                THEN 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' and '07:30:00'
                THEN 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' and '08:30:00'
                THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '07:30:00'), '%Y-%m-%d %H:%i:%s')
            ELSE NULL
        END AS StartRange,
        CASE
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '05:30:00' and '06:30:00'
                THEN 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' and '07:30:00'
                THEN 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' and '08:30:00'
                THEN STR_TO_DATE(CONCAT(CURDATE(), ' ', '08:30:00'), '%Y-%m-%d %H:%i:%s')
            ELSE NULL
        END AS EndRange,
        CASE
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '05:30:00' and '06:30:00'
                THEN '05:30:00-06:30:00'
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '06:31:00' and '07:30:00'
                THEN '06:31:00-07:30:00'
            WHEN DATE_FORMAT(dt,'%H:%i:%s') BETWEEN '07:31:00' and '08:30:00'
                THEN '07:30:00-08:30:00'
            ELSE NULL
        END AS TimeRange,
        COUNT(*) as number
    FROM report
    WHERE DATE_FORMAT(dt,'%Y:%m:%d')=DATE(CURDATE())
    GROUP BY StartRange, EndRange, TimeRange
    HAVING TimeRange IS NOT NULL
) sub0
LEFT OUTER JOIN report1
ON report1.dt BETWEEN sub0.StartRange AND sub0.EndRange
GROUP BY sub0.TimeRange, sub0.number

Upvotes: 1

Related Questions