I want to create a query, that will show the word “Passed” if the DATEDIFF(so_date,actual_delivery) = 3 then show “Failed” if not. Can someone help me please?
<?php $hostname="localhost"; $username="root"; $password=""; $db = "csl_otd"; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT so_date, actual_delivery, case when DATEDIFF(so_date, actual_delivery) = 3 then "Passed" else "Failed" end as status FROM sales_order LEFT JOIN dar ON sales_order.dar_numberr = dar.dar_number WHERE DATEDIFF(so_date, actual_delivery) < 10') as $row) { echo "<tr>"; echo "<td>" . $row['so_date'] . "</td>"; echo "<td>" . $row['actual_delivery'] . "</td>"; echo "<td>" . $row['DATEDIFF(so_date,actual_delivery)'] . "</td>"; echo "<td>" . $row['status'] . "</td>"; echo "</tr>"; } ?> </tbody></table>
Output
Date Difference | Status 2 | Passed 4 | Failed
Advertisement
Answer
use case when
SELECT so_date,actual_delivery, DATEDIFF(so_date,actual_delivery) Date_Difference, case when DATEDIFF(so_date,actual_delivery) = 3 then "Passed" else "Failed" end Status FROM sales_order LEFT JOIN dar ON sales_order.dar_numberr=dar.dar_number WHERE DATEDIFF(so_date,actual_delivery)<10
Or you can use if that you used (but there is little correction)
SELECT so_date,actual_delivery,DATEDIFF(so_date,actual_delivery), if ( DATEDIFF(so_date,actual_delivery) = 3 ,'Passed','Failed') status FROM sales_order LEFT JOIN dar ON sales_order.dar_numberr=dar.dar_number WHERE DATEDIFF(so_date,actual_delivery)<10