SQL order by 2 date columns

Question

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?

What datbase are you using? MySQL, Oracle? MSSQL? SQLite? They handle date data differently and with differenly-named functions. Any answer will probably be dependent on a specific vendor. — FrustratedWithFormsDesigner, May 08 '12 at 21:28

score 11 · Accepted Answer · answered May 08 '12 at 21:29

11

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

answered May 08 '12 at 21:29

JeffO

7,957
3
44
53

Thank you also for your reply. I will try both of these and see which suits best. Possibly this one as it merges the two dates under one name in the results. So I guess I need to put a check in my code so the same order doesnt get shown twice ? – Spunog May 08 '12 at 21:54
IVe learnt alot from this thanks a lot have perfect behaviour now :) – Spunog May 08 '12 at 22:21

score 8 · Answer 2 · answered May 08 '12 at 21:29

8

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

answered May 08 '12 at 21:29

pdr

6,372
1
29
38

Thanks for your help. Is it possible to label the dates as another name like 'DT_THE_DATE' whether its the collection date or the delivery date? – Spunog May 08 '12 at 21:43
@user1383132: MySql has a case statement (http://dev.mysql.com/doc/refman/5.0/en/case-statement.html) -- I'm sure you can translate it. – pdr May 08 '12 at 22:35

SQL order by 2 date columns

2 Answers2

Linked