[prog] Another newbie SQL question

[Chris Wilson]

Hi Tina,

On Wed, 28 Apr 2010, Tina wrote:

> I'm trying to do the following:
> 
> SELECT shipcity, shipstate
> FROM orders
> WHERE order# IN (SELECT order#
> FROM orders
>  GROUP BY order#
> HAVING MONTHS_BETWEEN(*) = (SELECT MAX(MONTHS_BETWEEN(shipdate, orderdate))
>  FROM orders
> GROUP BY order#));
> 
> The problem is I can't use HAVING MONTHS_BETWEEN(*). What should I do
> instead?

I'm not sure you've completely defined what you want to do. E.g. if 
shipdate is 2010-01-10 and orderdate is 2010-01-09, is that 0.029xxx or 
0.03 or 0.033 or 0 or 1 months between? How would you calculate it? Number 
of days (which is well defined) divided by 30?

I think it would be difficult to do even that using completely standard, 
portable ANSI SQL. You could use a MySQL extension such as DATEDIFF() and 
convert the number of days into a number of months instead.

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.