Saturday, October 14, 2006

Pranil's Solution

In an earlier post, i had said about a Sql Query.I had used 3 temporary table and it was always going to be a bad solution as far as performance is concerned.

Later in the day, my friend Prani Chacko came up with this solution which is way better , both in performance and simplicity.

select
(
convert(varchar,currentdate) + ' - ' +
convert(varchar,
(select top 1 currentdate from temperature C where c.currentdate > A.currentdate ))
) as DateRange ,
hot-(select hot from temperature as B where B.currentdate= (select top 1 currentdate from temperature D where D.currentdate > A.currentdate ) )
as DiffDate
from temperature as A
where A.currentdate < (select max(currentdate) from temperature)
order by A.currentdate asc