CODEWITHSUNDEEP
sql
Spunog
Spunog

Reputation: 309

SQL order by 2 date columns

I have a table with orders. Each order has a delivery_date, and a collection_date.

I want to retrieve the orders as a schedule for a week which shows what the next thing is to do.

So I guess I want the time sequence shown so that a collection on Wednesday appears in between a delivery on Tuesday and another delivery on Thursday.

Any ideas on how to do this with SQL?

Upvotes: 6

Views: 11359

Answers (2)

pdr
pdr

Reputation: 6440

Depending on which database you're using, it'll be something along the lines of

ORDER BY CASE WHEN delivery_date > collection_date 
         THEN collection_date ELSE delivery_date END

Upvotes: 9

JeffO
JeffO

Reputation: 8053

Using a Union ALL, I'm treating each of your order records as two records: one for a delivery and one for a collection. If you had another date (repair?), you would union another sub query with similar fields.

I made up a lot of this since you did not provide any specs on your order table.

select *
from (
   Select OrderID
       , 'Colection' as VisitType
      , Collection_Date as VisitDate
   from Orders
   Union All
   Select OrderID
     , 'Delivery' as VisitType
     , Delivery_Date as VisitDate
  from Orders
) as v
order by v.VisitDate

Upvotes: 11

Related Questions