mercredi 17 juin 2015

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

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!

Aucun commentaire:

Enregistrer un commentaire