mysql query logic

Question

I have an sql query which shows the delivery details of a vehicle. ( it uses greatest to fetch max value from a range of colums for each vehicle stop)

SELECT deliveryid AS deliverynumber, loadid1 AS loadnumberdate, 
       haulieraccepted AS haulier, 
       greatest(drop1arrivedatetime, drop2arrivedatetime, drop3arrivedatetime, 
                drop4arrivedatetime, drop5arrivedatetime) AS planneddate, 
       date(greatest(ActualDrop1Arrive, ActualDrop2Arrive, ActualDrop3Arrive, 
                     ActualDrop4Arrive, ActualDrop5Arrive )) AS actualenddate,
       mitigation
    FROM deliverydetails
    WHERE deliveryid=44

the output is

deliverynumber | loadnumberdate | haulier         | planneddate         | actualenddate | mitigation 
44             | 484487         | stols transport | 2011-11-26 15:50:00 | 2011-11-26    | customerdelay

How can I add to the mysql query to compare columns 'planneddate' and 'actualenddate'? if the dates are the same then set the query field to 'ontime' else if actualenddate>planneddate then 'deliverylate'. So ideally I want the following output:

deliverynumber | loadnumberdate | haulier         | planneddate         | actualenddate | mitigation    | Status
44             | 484487         | stols transport | 2011-11-26 15:50:00 | 2011-11-26    | customerdelay | ontime.

Thanks for the assistance.

outis · Accepted Answer · 2011-11-24 12:34:19Z

2

You can use a CASE statement or IF function. Perhaps something like:

SELECT ....,  IF(actualenddate>planneddate,'deliverylate','ontime') AS status FROM ....

edited Nov 24, 2011 at 12:34

outis

77.7k23 gold badges155 silver badges228 bronze badges

answered Nov 24, 2011 at 12:25

Giovanni

4,0253 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sonal Khunt · Accepted Answer · 2011-11-24 12:25:55Z

1

use mysql if condition and date conversion function to check and display according to....

answered Nov 24, 2011 at 12:25

Sonal Khunt

1,89612 silver badges20 bronze badges

Comments

Matt Fenwick · Accepted Answer · 2011-11-24 12:33:28Z

1

You can wrap your original query as a subquery. This will rename the columns. Then, use a case ... then clause to add the column.

Assuming your original query works just fine, it would look like this:

select
    *,
    case when (... some comparison on 'planneddate' and 'actualenddate' ...) 
         then <true output> 
             else <false output> end
from
    (<your original query>) as myalias;

The trick is that the columns from the subquery are renamed, allowing you to use their new names (planneddate and actualenddate).

answered Nov 24, 2011 at 12:33

Matt Fenwick

49.3k24 gold badges130 silver badges198 bronze badges

Collectives™ on Stack Overflow

mysql query logic

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related