Is there a way to re-write this SQL query using a WITH clause or any other CTE that might be even better?

Question

As you can see below, there is a SQL query inside of a SQL query inside of another SQL query. Is there any way I can give these queries an alias and call that alias in the proceeding query instead of re-writing it completely? I was trying to do it using a WITH clause but ended up with countless errors that I couldn't seem to get around. Any possible way to reduce this without having to re-write a query is pretty much what I'm looking for. I'm using Microsoft SQL Server 2005 by the way.

select 
MNumber, 
CDate,
(select MAX(CDate) 
from tbl_MeterCalib MC2 
WHERE MC2.MNumber= MC1.MNumber
AND CDate< MC1.CDate) as PrevCalDate,

datediff(d,(select MAX(CDate) 
from tbl_MeterCalib MC2 
WHERE MC2.MNumber= MC1.MNumber
AND CDate< MC1.CDate),CDate)/2 as AdjPeriod,

dateadd(d, -datediff(d,(select MAX(CDate) 
from tbl_MeterCalib MC2 
WHERE MC2.MNumber= MC1.MNumber
AND CDate< MC1.CDate),CDate)/2,CDate) as DaysBackTo

from tbl_MeterCalib MC1

Any help would be greatly appreciated! Thank you!

Answer 1

You won't be able to use a CTE, since the repeated select is dependent on a filter correlating back to the current row.

However, you can use a CROSS APPLY to a derived table to DRY up the repetition:

select 
    MNumber, 
    CDate,
    x.MaxCDate as PrevCalDate,
    datediff(d,x.MaxCDate,CDate)/2 as AdjPeriod,
    dateadd(d, -datediff(d,x.MaxCDate,CDate)/2,CDate) as DaysBackTo
from tbl_MeterCalib MC1
    CROSS APPLY 
    (
        select MAX(CDate) AS MaxCDate
        from tbl_MeterCalib MC2 
        WHERE MC2.MNumber= MC1.MNumber
            AND CDate< MC1.CDate
    ) x;

SqlFiddle