CODEWITHSUNDEEP
mysqlsqldate
Matt
Matt

Reputation: 1131

MySQL query: ORDER BY date plus other record

I have a table with: 

last_billed DATE DEFAULT "0000-00-00"
bill_interval varchar(10) DEFAULT "Monthly" (other values are: Quarterly, Half-yearly, Yearly)

I run a simple query, but it needs to: 

ORDER BY (last_billed + bill_interval)

This should give a date which is 1 month, 3 months, 6 months or 12 months further then the last_billed date, depending on the value of bill_interval. Now I can change bill_interval to anything if need be, if it would make the query possible.

I have no idea where to start with this. So any feedback is, as always, appreciated.

Upvotes: 0

Views: 77

Answers (2)

John Bingham
John Bingham

Reputation: 2006

ORDER BY DateAdd("m", 
  case substring(bill_interval,1,1) when 'M' then 1 
                                    when 'Q' then 3 
                                    when 'Y' then 12 
  end, last_billed)

Upvotes: 0

Barmar
Barmar

Reputation: 780899

Try:

ORDER BY last_billed + 
    CASE bill_interval
        WHEN "Montly" THEN INTERVAL 1 MONTH
        WHEN "Half-Yearly" THEN INTERVAL 6 MONTH
        WHEN "Yearly" THEN INTERVAL 1 YEAR
    END

It would be easier if you changed the bill_interval column to a number of months, then you could do:

ORDER BY last_billed + INTERVAL bill_interval MONTH

Upvotes: 1

Related Questions