Convert a paramaterized query into a view in SQL Server -

suppose had following table in sql server:

date         cola         colb 1/1/2013     val1a        val1b 1/1/2013     val2a        null 1/1/2013     val3a        val3b 1/2/2013     val1a        null 1/2/2013     val2a        val4b 

and, i'm looking see day-over-day changes colb - which, in case follows:

date         cola         colb_today       colb_prev_day 1/2/2013     val1a        null             val1b 1/2/2013     val2a        val4b            null 1/2/2013     val3a        null             val3b 

so, created complex query (joining table itself) has single input variable , calculated variable looking follows:

declare @date date = '1/2/2013', @prevday date; set @prevday = (select top 1 date mytable date < @date order date desc);  select   @date 'date',   t1.cola,   t1.colb colb_today,   t2.colb colb_prev_day   (select * mytable date = @date) t1,  full outer join   (select * mytable date = @prevday) t2  on   t1.cola = t2.cola 

which i'm looking for.

now, i'd able convert view (with no parameters being passed) rather paramaterized query.

i hope i'm looking makes sense... in other words, view have columns date, cola, colb_today & colb_prev_day , pulls data table create same result without having paramaterize date (simply, based upon dates available in table) - biggest challenge seems val3a row above has value 1 of 2 dates, not both...

any thoughts???

thanks!!

you can use ugly this:

with cte_dates (     select distinct [date] mytable ), cte_vals (     select distinct cola mytable ) select     d.[date],     v.cola,     t1.colb colb_today,     t2.colb colb_prev_day cte_dates d     cross join cte_vals v     left outer join mytable t1 on t1.cola = v.cola , t1.[date] = d.[date]     outer apply (         select top 1 tt.colb         mytable tt         tt.cola = v.cola , tt.[date] < d.[date]         order tt.[date] desc     ) t2 

=> sql fiddle demo

it's hard better until know more data. have gaps between dates in table?