How can I use calculated alias in Order by clause?

Question

The table is simple:

ID   start_date    end_date
1    2015-10-01    2015-10-02
2    2015-10-02    2015-10-03
3    2015-10-05    2015-10-06
4    2015-10-07    2015-10-08

ID 1 and 2 belong to one project since the end_date equals to the start_date, ID 3 and 4 are different ones.

Here is the query to find the same projects and sort by the time they take:

select P1.Start_Date, (
    select min(P.End_Date)
    from Projects  as P
    where P.End_Date not in (select Start_Date from Projects )
        and P.End_Date > P1.Start_Date
) as ED
from Projects as P1
where P1.Start_Date not in (select End_Date from Projects )
order by datediff(day, P1.Start_Date, ED)

The problem is: the ED is invalid in the order by clause, but when using without datediff is valid:

order by ED

Is datediff calculated after select clause? Any one could explain? Thanks.

Answer 1

You can simply use CROSS APPLY to calculate this column like this:

DECLARE @Projects TABLE
(
    [ID] SMALLINT
   ,[start_date] DATETIME
   ,[end_date] DATETIME
);

INSERT INTO @Projects ([ID], [start_date], [end_date])
VALUES ('1', '2015-10-01', '2015-10-02')
      ,('2', '2015-10-02', '2015-10-03')
      ,('3', '2015-10-05', '2015-10-06')
      ,('4', '2015-10-07', '2015-10-08');

select P1.Start_Date, ED
from @Projects as P1
CROSS APPLY
(
    select min(P.End_Date)
    from @Projects  as P
    where P.End_Date not in (select Start_Date from @Projects )
        and P.End_Date > P1.Start_Date
) DS(ED)
where P1.Start_Date not in (select End_Date from @Projects )
order by datediff(day, P1.Start_Date, ED);

It seems that the engine of the management studio is not able to translate the alias ED to something valid. If for example you replace the ED with the sub-query it will work. Also, the following which is a bad practice will work:

select P1.Start_Date, (
    select min(P.End_Date)
    from @Projects  as P
    where P.End_Date not in (select Start_Date from @Projects )
        and P.End_Date > P1.Start_Date
) as ED
from @Projects as P1
where P1.Start_Date not in (select End_Date from @Projects )
order by datediff(day, P1.Start_Date, 2)

Instead alias we are using the number of the column on which to sort. So, there is nothing wrong with your code.