CODEWITHSUNDEEP
mysqlselect
livinzlife
livinzlife

Reputation: 873

mysql select with changing date based on column

I have a table of orders, there are two types of orders. "fix" and "new". All orders have an orderDate. I need to order the results of a mysql select in an unusual way.

If the order is type "fix", it will have a due date of the order date PLUS 1 day.

If the order is type "new", it will have a due date of the order date PLUS 2 days.

I need to order by the due date, but the due date is something that does not exist in a column. How can I do this?

Upvotes: 0

Views: 23

Answers (3)

RuslanN
RuslanN

Reputation: 408

As i read from you question you need to order by due_date Here is the code orderin by due_dateenter code here:

SELECT 
    *
FROM
    tablename
ORDER BY CASE order_type
    WHEN 'fix' THEN order_date + INTERVAL 1 DAY
    ELSE order_date + INTERVAL 2 DAY
END DESC;

Upvotes: 2

void
void

Reputation: 7880

use case statement with date_add() function:

select type, case order.type
                        when 'fix' then date_add(OrderDate,interval 1 DAY)
                        else then date_add(OrderDate,interval 2 DAY)
                   end order_date
from table_name
order by order_date

Upvotes: 0

Gervs
Gervs

Reputation: 1397

You can generate a virtual column based on that conditions and order by that column:

SELECT
     CASE order_type
         WHEN 'fix' THEN datecolumn + INTERVAL 1 DAY
         ELSE datecolumn + INTERVAL 2 DAY END order_date
FROM
    tablename
ORDER BY order_date DESC

Upvotes: 0

Related Questions