SQL - Using datediff as a query is running?

Question

Hi all,

Its my first post and I'm really rusty on MSSQL so be gentle :-)

I have a table in which I am trying to use datediff. I think it will be easiest if I post the query and results first

select mh.agetime, mh.whatid

from mailhistory mh
inner join mail m
 on mh.mailid=m.myid
where (mh.whatid=17 or mh.whatid=11 or mh.whatid=0) and maincontactid=287816 and mailid=276086

order by agetime

Really, the maincontactid and mailid are currently just in there to limit the results while i make the query.

The results are as follows...

AGETIME                    WHATID
1899-12-30 00:00:00.000 0
1899-12-30 00:48:10.000 11
1899-12-31 02:16:49.000 17
1899-12-31 06:29:08.000 11
1900-01-18 15:31:40.000 17
1900-02-11 14:56:59.000 11

I am trying to make a third column as the query runs that will make a third column showing the difference in the dates (in days)... between items with a WHATID of 11 and 17... so I'm after results like this:

AGETIME                    WHATID    DIFFERENCE
1899-12-30 00:00:00.000 0         NULL
1899-12-30 00:48:10.000 11        0
1899-12-31 02:16:49.000 17        1
1899-12-31 06:29:08.000 11        0
1900-01-18 15:31:40.000 17        18
1900-02-11 14:56:59.000 11        22

Something like that... So, is there a way to convert my query to do the running datediff like that?

Many thanks in advance!

Chris

Answer 1

If using SQL Server 2005 or above, you can make a CTE and assign row_number to your current resultset. Then you can have a left self join on CTE with Current.Row_Num = Previous.Row_Num -1 and then can get the date difference. Approach will be similar as show in this link:

http://www.kodyaz.com/articles/sql-select-previous-and-next-rows-with-current-row-in-tsql.aspx

For SQL Server 2000

Assuming there is no grouping in result set:

I would probably create a temp table variable instead of CTE with an Identity Filed( Which will act as a rownumber) and then will apply the same logic as described above for 2005. Only major change will be instead of CTE we will use Temp Table variable and instead of rownumber we will use identity in temp table.