Diff b/w time in two rows in sql server

Question

hi, i have a sitaution where i need to compare time in two diff rows,its like this

categoryid  item_id  group  date
     1        10      abc   2008-03-07 03:02:00
     1        35      bcd   2008-04-03 10:03:00
     2        13      cde   2008-03-13 07:18:00
     2        40      ced   2008-03-13 08:41:00
     2        44      cef   2008-03-13 09:41:00

i need to find the time diff for row one and row 2 - they fall under cat one ... and it is under it continue and cat1

Answer 1

Try this(not tested though)

SELECT datediff(day,t.date,d.date) diff
  FROM @t d 
  JOIN @t t ON d.categoryid = t.categoryid
 and t.itemid <> d.itemid
 and datediff(day,t.date,d.date) >0

Answer 2

I'd phrase it like this, but the principal is the same

SELECT r1.item_id AS r1Id, r2.item_id AS r2Id, datediff(day, r1.date, r2.date) AS diff
FROM @t r1
INNER JOIN @t r2 ON r1.categoryid = r2.categoryid
WHERE r1.item_id != r2.item_id

Answer 3

Firstly, thanks a lot to you all. Sorry for not indenting the question properly.And I got it working, but with a stored procedure,

wrote a while loop to loop through all the category id's, item id's ... get them into variables and the assigning them to a query where i was calculating the datediff between those two

I am not able to put the code over here as the question was posted from office and now i am at home. anyways thanks all.