;With Dates
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Date) AS Seq,Date
FROM Table
)

SELECT d1.[Date],
DATEDIFF(minute,COALESCE(d2.[date],d1.[date]),d1.[date]) AS Difference
FROM Dates d1
LEFT JOIN Dates d2
ON d2.Seq=d1.Seq-1

quote:

---

Originally posted by jkuhn76

Hmm, I get this message: The OVER SQL construct or statement is not supported?

Here is what I tried - feel free to laugh - pretty new to doing queries

SELECT ROW_NUMBER() OVER (ORDER BY Illusion_Notes.NoteDate) AS Seq,Illusion_Notes.NoteDate,
DATEPART(m, Illusion_Task.DateOpened) AS 'MonthNumber', Illusion_Task.TaskID, Illusion_Staff.Username, Illusion_TaskType.TaskTypeDescription,
Illusion_Staff_1.Username AS NoteUser, CAST(Illusion_Notes.Notes AS NVARCHAR(MAX)) AS Notes, Illusion_Notes.NoteDate AS FirstNoteDate,
Illusion_Notes_1.NoteDate AS SecondNoteDate, Illusion_Notes.NoteDate, DATEDIFF(Minute, COALESCE (Illusion_Notes_1.NoteDate, Illusion_Notes_1.NoteDate),
Illusion_Notes.NoteDate) AS Difference, Illusion_Notes.NoteID AS FirstNoteID
FROM Illusion_Task INNER JOIN
Illusion_Staff ON Illusion_Task.AssignedToStaffID = Illusion_Staff.StaffID INNER JOIN
Illusion_TaskType ON Illusion_Task.TaskTypeID = Illusion_TaskType.TaskTypeID INNER JOIN
Illusion_Notes AS Illusion_Notes ON Illusion_Task.TaskID = Illusion_Notes.TaskID INNER JOIN
Illusion_Staff AS Illusion_Staff_1 ON Illusion_Notes.StaffID = Illusion_Staff_1.StaffID LEFT OUTER JOIN
Illusion_Notes AS Illusion_Notes_1 ON Illusion_Notes.NoteDate = Illusion_Notes_1.NoteDate - 1
WHERE (Illusion_Staff.Username = 'cott') AND (DATEPART(m, Illusion_Task.DateOpened) = @Month) AND (Illusion_Task.TaskID = '181911')
GROUP BY Illusion_Notes.NoteDate, Illusion_Task.DateClosed, Illusion_Task.TaskID, Illusion_Staff.Username, Illusion_TaskType.TaskTypeDescription,
Illusion_Notes.NoteDate, Illusion_Staff_1.Username, CAST(Illusion_Notes.Notes AS NVARCHAR(MAX)), Illusion_Notes.NoteDate, Illusion_Notes.NoteDate,
Illusion_Notes.NoteDate, Illusion_Notes.NoteDate, Illusion_Task.DateOpened, Illusion_Notes.NoteID, Illusion_Notes_1.NoteDate

---

SELECT @@VERSION
GO
EXEC sp_dbcmptlevel 'database name'
GO

quote:

---

Originally posted by jkuhn76

Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

---

;With Temp
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY  Illusion_Notes.NoteDate) AS Seq,...
rest of the query

)

SELECT t1.*, DATEDIFF(minute,COALESCE(t2.NoteDate,t1.NoteDate),t1.NoteDate) AS Difference
FROM Temp t1
LEFT JOIN Temp t2
ON t2.Seq= t1.Seq-1

DECLARE @Technician varchar(60),@Month int

SELECT @Technician = 'your value',@month = your value

; WITH Temp AS (SELECT ROW_NUMBER() OVER (ORDER BY Illusion_Notes.NoteDate) AS Seq, DATEPART(m, Illusion_Task.DateOpened) AS 'MonthNumber', 
Illusion_Task.TaskID, Illusion_Staff.Username, Illusion_TaskType.TaskTypeDescription, Illusion_Staff_1.Username AS NoteUser, 
CAST(Illusion_Notes.Notes AS NVARCHAR(MAX)) AS Notes, Illusion_Notes.NoteDate, Illusion_Notes.NoteID
FROM Illusion_Task INNER JOIN
Illusion_Staff ON Illusion_Task.AssignedToStaffID = Illusion_Staff.StaffID INNER JOIN
Illusion_TaskType ON Illusion_Task.TaskTypeID = Illusion_TaskType.TaskTypeID INNER JOIN
Illusion_Notes AS Illusion_Notes ON Illusion_Task.TaskID = Illusion_Notes.TaskID INNER JOIN
Illusion_Staff AS Illusion_Staff_1 ON Illusion_Notes.StaffID = Illusion_Staff_1.StaffID INNER JOIN
Illusion_Notes AS Illusion_Notes_1 ON Illusion_Notes.NoteDate = Illusion_Notes_1.NoteDate
Where Illusion_Staff.Username = @Technician and DATEPART(m, Illusion_Task.DateOpened) = @Month
GROUP BY Illusion_Task.DateClosed, Illusion_Task.TaskID, Illusion_Staff.Username, Illusion_TaskType.TaskTypeDescription, Illusion_Notes.NoteDate, 
Illusion_Staff_1.Username, CAST(Illusion_Notes.Notes AS NVARCHAR(MAX)), Illusion_Task.DateOpened, Illusion_Notes.NoteID)
SELECT t1.*, DATEDIFF(minute, COALESCE (t2.NoteDate, t1.NoteDate), t1.NoteDate) AS Difference
FROM Temp t1 LEFT JOIN
Temp t2 ON t2.Seq = t1.Seq - 1