I'm currently working on some reporting for an eCommerce system that needs to show how long an order has been delayed. I have a log of projected delivery dates for the order and am able to get the initial and last dates, but doing more complex things like comparing the projected dates is proving problematic. 

A (simplified version of) my query is as follows: 

```
SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection
FROM orders
-- and some other joins here for additional report data

```

(Just FYI, I'm sorting on IDs rather than dates here because dispatch projections can move forward as well as back, for example if a stock shipment comes in ahead of schedule). 

This is fine for extracting some raw data about the projected delivery history for an order, but I want to do some additional analysis of the data, for example how many days difference there is between the initial projected shipping date, the final projected shipping date and the actual shipping date. This is where I run into trouble. 

I tried adding a `DATEDIFF(final_delivery_projection, initial_delivery_projection)` column to my SELECT clause in order to see how many days back a given delivery prediction has slipped, but MySQL wouldn't have it. 

```
SELECT orders.order_id,
       orders.date_dispatched AS actual_dispatch_date,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id ASC
           LIMIT 1
       ) AS initial_delivery_projection,
       (
           SELECT projected_date 
           FROM order_delivery_projections
           WHERE order_id = orders.order_id
           ORDER BY order_delivery_projection_id DESC
           LIMIT 1
       ) AS final_delivery_projection,
       DATEDIFF(final_delivery_projection - initial_delivery_projection) AS projection_days_revised
FROM orders
-- and some other joins here for additional report data

```

> 
> Unknown column final\_delivery\_projection in field list
> 
> 
> 

Presumably you can't use an alias in a select statement if the alias is referring to a subselect in the same statement. 

I would also like to be able to exclude orders in the WHERE clause based on the results of the projections. For example, I'd like to exclude all orders where the final projected shipping date falls before the initial projected date on the grounds that I'm only interested in orders that are being held up rather than ones that have shipped ahead of schedule. 

Is extracting and processing the data I'm trying to get in the same statement possible, or will I have to do some post-processing in the client to work things like this out? If it is possible in SQL, then how can it be done?