SQLTeam.com | Weblogs | Forums

SQL Server - reading records and getting the difference between the dates


#1

Hi my data is as follows:
Oppo_OpportunityId Oppo_Stage Oppo_CreatedDate ranking
248072 QUOTED 2015-12-01 07:58:18.000 1
248072 QUOTED 2015-12-01 08:07:49.000 2
248072 Amend 2016-06-24 10:59:22.000 3
248072 AS 2016-06-27 11:45:35.000 4
248072 QUOTING 2016-06-27 15:54:04.000 5
248072 QUOTED 2016-06-29 16:03:05.000 6
248072 QUOTED 2016-06-29 16:12:06.000 7
248072 Amend 2016-07-20 12:00:48.000 8
248072 AS 2016-07-21 07:07:09.000 9
248072 QUOTING 2016-07-22 07:16:22.000 10
248072 QUOTED 2016-07-26 10:30:25.000 11
248072 QUOTED 2016-07-26 10:41:39.000 12

How do I in sql server read each of these records and then get the difference in the datetime values. For example,
QUOTING 2016-07-22 07:16:22.000 next record QUOTED 2016-07-26 10:30:25.000; the difference as a value to appear by the first record - hope this makes sense. I have the code to get the difference just unsure how to code this, thought of using ROW_NUMBER().

Hope someone can help me


#2

use LEAD/LAG, partition by oppo_opportunityid, order by date. then DATEDIFF will get you the result you want