Hello all,
I have a DB that is a history of items checked out,
hw_id, type, chk_out
D29W4826, WT41N0, 2018-01-02 12:00:00
D29W2F9D, MC92N0, 2018-01-02 12:01:02
D29W4826, WT41N0, 2018-01-02 03:00:00
notice that each ID can be checked out multiple times a day …
i have a query:
SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM checkinout WHERE chk_out BETWEEN “2018-8-01 00:00:00” AND “2018-8-30 00:00:00” AND hw_type IN(“WT41N0”) GROUP BY hw_id, hw_type ORDER BY Count Desc
The above code results in the below:
Device Usage
Total Devices Used = 211
Total Devices Onhand = 212
Daily Utilization Used = 100%
| Device | Type | Count |
|---|---|---|
| D29W4826 | WT41N0 | 35 |
| D29W2F9D | WT41N0 | 34 |
| D29W5F96 | WT41N0 | 33 |
This is the PHP i am using to generate this report:
<?php
$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = 'SELECT COUNT(hw_type) AS Count,hw_type AS Type,hw_id AS Device FROM `checkinout` WHERE chk_out BETWEEN "2018-8-01 00:00:00" AND "2018-8-30 00:00:00" AND hw_type IN("WT41N0") GROUP BY hw_id, hw_type ORDER BY Count Desc';
$result = $conn->query($sql);
$row_cnt = mysqli_num_rows($result);
$onhand = "212";
$test = round($row_cnt/$onhand*100);
echo '<br>Total Devices Used = '.$row_cnt;
echo '<br>Total Devices Onhand = '.$onhand;
echo '<br>Daily Utilization Used = '.$test.'%';
while($row = $result->fetch_assoc()) {
echo "<tr><td>".$row['Device']."</td>";
echo "<td>".$row['Type']."</td>";
echo "<td>".$row['Count']."</td></tr>";
}
$conn->close();
?>
What i am trying to accomplish is get the SUM of the Count column so i can get a per day avg Utilz of the devices over time as you can see the $test var is wrongly calculated as it does not have the correct numbers.