Jump to content

selecting distinct items and listing vehicles last cleaned based on criteria


PatrickO
 Share

Recommended Posts

Hi all, i've drawn a blank on how to do this task. Something I did years ago, but the brain ain't engaging gear.

I have two tables, one is vehicle cleaning and the other is vehicle specs.

I need a report where it'll select the distinct vehicles from the vehicle specs table, find the last time the vehicle was cleaned from the vehicle cleaning table based on a specific criteria, and refer back to vehicle_specs for the column of frequency to display whether the vehicle has been cleaned within the set time frame.

Link to comment
Share on other sites

Is this what you require?

 

INSERT INTO `vehicle_cleaning` (`id`, `bus_no`, `date_cleaned`, `cleaner`, `washed`, `headtail`, `surfaces`, `rails`, `seats`, `cabin`, `windows`, `mop`, `tyres`, `washer`, `acfilter`, `additional`, `repairs`, `defects`) VALUES
(18, 'DV-77', '2022-04-18', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(19, 'EV-12', '2022-04-21', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(20, 'DV-19', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', 'Requires A/C Filter (none fitted)'),
(21, 'DV-82', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(22, 'DV-62', '2022-04-17', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', '');
 

 

INSERT INTO `vehicle_specs` (`id`, `bus_no`, `date_inservice`, `date_outservice`, `rego`, `type`, `frequency`) VALUES
(1, 'EV-01', '2018-01-01', '0000-00-00', 'XQ63MV', 'Electric | Rigid | 3 door', 5),
(2, 'EV-02', '2018-01-01', '0000-00-00', '914XZP', 'Electric | Rigid | 3 door', 5),
(3, 'EV-03', '2018-01-01', '0000-00-00', '913XZP', 'Electric | Rigid | 3 door', 5),
(4, 'EV-04', '2018-01-01', '0000-00-00', '915XZP', 'Electric | Rigid | 3 door', 5),
(5, 'EV-05', '2018-01-01', '0000-00-00', '916XZP', 'Electric | Rigid | 3 door', 5);

Link to comment
Share on other sites

Sorry, this is a better snapsnot of what it needs to do.

 

INSERT INTO `vehicle_cleaning` (`id`, `bus_no`, `date_cleaned`, `cleaner`, `washed`, `headtail`, `surfaces`, `rails`, `seats`, `cabin`, `windows`, `mop`, `tyres`, `washer`, `acfilter`, `additional`, `repairs`, `defects`) VALUES
(18, 'EV-01, '2022-04-18', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(19, 'EV-02', '2022-04-21', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(20, 'EV-03', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', 'Requires A/C Filter (none fitted)'),
(21, 'EV-02', '2022-04-22', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', '', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', ''),
(22, 'EV-05', '2022-04-17', 'Patrick', 'Y', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Degreased WC & Doorwells, Gumptioned Dash', '', '');
 

 

INSERT INTO `vehicle_specs` (`id`, `bus_no`, `date_inservice`, `date_outservice`, `rego`, `type`, `frequency`) VALUES
(1, 'EV-01', '2018-01-01', '0000-00-00', 'XQ63MV', 'Electric | Rigid | 3 door', 5),
(2, 'EV-02', '2018-01-01', '0000-00-00', '914XZP', 'Electric | Rigid | 3 door', 5),
(3, 'EV-03', '2018-01-01', '0000-00-00', '913XZP', 'Electric | Rigid | 3 door', 5),
(4, 'EV-04', '2018-01-01', '0000-00-00', '915XZP', 'Electric | Rigid | 3 door', 5),
(5, 'EV-05', '2018-01-01', '0000-00-00', '916XZP', 'Electric | Rigid | 3 door', 15);

 

So what the script would do is look for the distinct bus_no in vehicle_cleaning, and report that:

EV-01 was last cleaned on 2022-04-18, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-02 was last cleaned on 2022-04-22, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-03 was last cleaned on 2022-04-22, and as it's more than 5 days since todays date that it was cleaned, it'll list the vehicle as requiring cleaning.
EV-05 was last cleaned on 2022-04-17, and as it's less than 15 days since todays date, it wouldn't show the vehicle is due to be cleaned.

Link to comment
Share on other sites

Nothing so far, that's where the mind has just gone blank.

 

I know the distinct is required but then referencing the last record for each vehicle cleaning date & whether its < or > than the frequency is where I draw a blank.

Link to comment
Share on other sites

Try

SELECT v.bus_no
     , v.rego
     , COALESCE(last_cleaned, date_inservice) as last_cleaned
     , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
     , v.frequency
     , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
            THEN 'REQUIRED'
            ELSE 'OK'
            END as cleaning
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no);
     
+--------+--------+--------------+----------+-----------+----------+
| bus_no | rego   | last_cleaned | days_ago | frequency | cleaning |
+--------+--------+--------------+----------+-----------+----------+
| EV-01  | XQ63MV | 2022-04-18   |       12 |         5 | REQUIRED |
| EV-02  | 914XZP | 2022-04-22   |        8 |         5 | REQUIRED |
| EV-03  | 913XZP | 2022-04-22   |        8 |         5 | REQUIRED |
| EV-04  | 915XZP | 2018-01-01   |     1580 |         5 | REQUIRED |
| EV-05  | 916XZP | 2022-04-17   |       13 |        15 | OK       |
+--------+--------+--------------+----------+-----------+----------+

Note EV-04 hasn't been cleaned since it came into service - so must be due by now!

Link to comment
Share on other sites

15 minutes ago, Barand said:
SELECT v.bus_no
     , v.rego
     , COALESCE(last_cleaned, date_inservice) as last_cleaned
     , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
     , v.frequency
     , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
            THEN 'REQUIRED'
            ELSE 'OK'
            END as cleaning
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no);

Wow, that worked better than I thought, and you've used terms i've never known, would hate to see what my code would've looked like.

Any chance you can convert that to php code to show the results?

Link to comment
Share on other sites

Just this once

<?php
const HOST     = 'localhost';                                                          
const USERNAME = '????';                                                               
const PASSWORD = '????';                                                               
const DATABASE = 'test';               // default db                                  
                                                                                
function pdoConnect($dbname=DATABASE)                                                  
{                                                                                      
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                      
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                 
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                              
    return $db;                                                                        
}                                                                                      

$pdo = pdoConnect();
 
$result = $pdo->query("SELECT  v.bus_no as BusNo
                             , v.rego as RegNo
                             , COALESCE(last_cleaned, date_inservice) as last_cleaned
                             , DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) as days_ago
                             , v.frequency
                             , CASE WHEN DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
                                    THEN 'REQUIRED'
                                    ELSE 'OK'
                                    END as cleaning
                        FROM vehicle_specs v
                             LEFT JOIN
                             (
                                SELECT bus_no
                                     , MAX(date_cleaned) as last_cleaned
                                FROM vehicle_cleaning
                                GROUP BY bus_no
                             ) c USING (bus_no)");
$row = $result->fetch();
$tdata = '';
$theads = "<tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
    $tdata .= "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $result->fetch()) ;
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-language" content="en">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<head>
<title>Example</title>
<style type='text/css'>
    body {
        font-family: calibri, sans-serif;
        font-size: 12pt;
    }
    table {
        border-collapse: collapse;
        width: 600px;
    }
    th {
        background-color: black;
        color: white;
        padding: 8px;
    }
    td {
        padding: 4px 8px;
    }
</style>
</head>
<body>
    <table border='1'>
        <?= $theads ?>
        <?= $tdata ?>
    </table>
</body>
</html>

image.png.0156fc070d0da128c99ed6cb91b25b58.png

Edited by Barand
add output image
Link to comment
Share on other sites

That was amazing, thank you. The report is working perfectly. Downside is I was getting a few "-1" in the days_ago field. I suspect its because the timezone line wasn't added to the page.

If I was to have the field names defined and only show just what is required, how do i put that into the script?

I'm thinking something like:
SELECT bus_no , MAX(date_cleaned) as last_cleaned FROM vehicle_cleaning GROUP BY bus_no ) c USING (bus_no) WHERE "requested"")

but i know that's not going to work.

Link to comment
Share on other sites

The "days_ago" will evaluate to a negative number if the date_cleaned or date_inservice is in the future.

date_cleaned should always be historical (or it's an error) but it is feasible that there may be new buses in the table which come into service in the next few days.

We can check that only current buses are reported by

WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice)

If you only want to list buses where cleaning is required, the condition would be

WHERE DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency

If you only want to show the bus no and date_last_cleaned, only select those.

Then the query becomes

SELECT v.bus_no as 'Bus No'
     , COALESCE(last_cleaned, date_inservice) as 'Last Cleaned'
FROM vehicle_specs v
     LEFT JOIN
     (
        SELECT bus_no
             , MAX(date_cleaned) as last_cleaned
        FROM vehicle_cleaning
        GROUP BY bus_no
     ) c USING (bus_no)
WHERE CURDATE() BETWEEN date_inservice AND IF(date_outservice=0, '9999-12-31', date_outservice)
      AND DATEDIFF(CURDATE(), COALESCE(last_cleaned, date_inservice)) >= frequency
      
+--------+--------------+
| Bus No | Last Cleaned |
+--------+--------------+
| EV-02  | 2022-04-22   |
| EV-03  | 2022-04-22   |
| EV-04  | 2018-01-01   |
+--------+--------------+

 

Link to comment
Share on other sites

Worked like a pearler, thank you :)

 

One last question to close this one off, i've got 
date_default_timezone_set("Australia/Brisbane");

located in my index.php file, but until around 10 or 11am each day, the pre loaded date field shows the previous date. Am I doing something wrong?
 

Link to comment
Share on other sites

Actually I have another question, and i've tried a couple of different ways all to no avail.

In mysql, this syntax works and displays the desired results of how many times a vehicle has been cleaned.
SELECT bus_no, COUNT(*) FROM vehicle_cleaning WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' GROUP BY bus_no

but when I put it into php, it doesn't work.
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
//foreach($dbh->query('SELECT bus_no,date_cleaned COUNT(*) FROM vehicle_cleaning GROUP BY bus_no') as $row) {
foreach($dbh->query("SELECT * FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' GROUP BY bus_no") as $row) {    
echo "<tr>";
echo "<td>" . $row['bus_no'] . "</td>";
echo "</tr>";
}

 

Ultimately, i'll be wanting to have a search criteria either by month or by week to get the results.

Link to comment
Share on other sites

Posted (edited)

Have you tried it with COUNT(*) in php?

foreach($dbh->query("SELECT bus_no
                          , COUNT(*) as total 
                    FROM `vehicle_cleaning` 
                    WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' 
                    GROUP BY bus_no") as $row) {    
    echo "<tr>";
    echo "<td>" . $row['bus_no'] . "</td>";
    echo "<td>" . $row['total'] . "</td>";
    echo "</tr>";
} 

Re your datetime problem, what is the timezone setting on your mysql server?

Edited by Barand
Link to comment
Share on other sites

Sadly, that didn't work.

Here is the full code:
 

<table class='table table-bordered'>
<tr>
<th>Bus No</th><th>No of Cleans</th>
</tr>
<?php
$hostname="localhost";
$username="xxx";
$password="xxx";
$db = "xxx";

$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
//foreach($dbh->query('SELECT bus_no,date_cleaned COUNT(*) FROM vehicle_cleaning GROUP BY bus_no') as $row) {
foreach($dbh->query("SELECT bus_no
                          , COUNT(*) as total 
                    FROM `vehicle_cleaning` 
                    WHERE date_cleaned BETWEEN '2022-04-25' AND '2022-05-01' 
                    GROUP BY bus_no") as $row) {    
    echo "<tr>";
    echo "<td>" . $row['bus_no'] . "</td>";
    echo "<td>" . $row['total'] . "</td>";
    echo "</tr>";

?>
</table>
 

Also unsure how to check timezone in mysql server. It's shared hosting if that helps.

Link to comment
Share on other sites

Got it working, just having difficulty with the input boxes for the search:

I'm missing something, just not sure.

<form action="vehicle_cleaning-stats.php" method="post">
<input type="date" name="date_from"><input type="date" name="date_to"><input type="submit" value="Search">
</form>

$result = $pdo->query("SELECT bus_no, COUNT(*) as total FROM `vehicle_cleaning` WHERE date_cleaned BETWEEN '$date_from' AND '$date_to' GROUP BY bus_no");
 

Link to comment
Share on other sites

Your input dates will be in the $_POST array.

Don't put user input data directly into your sql, use prepared queries.

 <form action="vehicle_cleaning-stats.php" method="post">
<input type="date" name="date_from"><input type="date" name="date_to"><input type="submit" value="Search">
</form>

php

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $stmt = $pdo->prepare("SELECT bus_no
                                , COUNT(*) as total
                           FROM `vehicle_cleaning` 
                           WHERE date_cleaned BETWEEN ? AND ? 
                           GROUP BY bus_no
                           ");
    $stmt->execute( [ $_POST['date_from'], $_POST['date_to'] ] );
    echo "<table>";
    foreach ($stmt as $row) {
        echo "<tr>";
        echo "<td>" . $row['bus_no'] . "</td>";
        echo "<td>" . $row['total'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
}

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

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.