sql server - Create weighted average in SQL using dates -

i have sql query lists details item. works should except last column. want weight of transaction column report difference in days.

so example 4th row in txdate column 05/21/2014 , 3rd row 05/12/20014. weight of transaction column in 4th row should 9.

i read lag , lead functions, i'm not sure how implement dates (if it's possible). if isn't possible there way accomplish this?

select t.txnumber,     t.item,     t.txcode,     t.txdate,     (t.onhandlocold + t.stockqty) 'ending quantity',    tmax.maxtnumber 'latest transaction code',    tmax.maxdate 'latest transaction date',     tmin.mindate 'first transaction date',     (t.txdate - tmin.mindate) 'weight of transaction'  tbliminvtxhistory t  left outer join      (select t.item, max(t.txnumber) maxtnumber, max(t.txdate) maxdate       tbliminvtxhistory t       t.txcode != 'paway'       group item) tmax   on t.item = tmax.item left outer join      (select t.item, min(t.txdate) mindate       tbliminvtxhistory t        t.txcode != 'paway'          , t.txdate > dateadd(year, -1, getdate())       group item) tmin   on t.item = tmin.item  t.item = 'lr50m'  , t.txcode != 'paway' , t.txdate > dateadd(year, -1, getdate()) 

check out datediff function, return difference between 2 dates.

i think looking for:

datediff(dd,tmin.mindate,t.txdate) 

update:

now understand question little better, here update. mentioned in comment on above post, lag function supported in sql 2012 , up. alternative use row_number , store results temp table. can left join same table on next row_number in results. use datediff compare dates. exact same thing the lag function.

example:

select row_number() on (order txdate) rownumber,* #rows tbliminvtxhistory   select datediff(dd,r2.txdate,r.txdate),*  #rows r left join #rows r2 on r.rownumber=r2.rownumber+1  drop table #rows