DATEDIFF issue between records in the same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

DATEDIFF issue between records in the same column

Author

Topic

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-11 : 14:51:01

Hoping for some help with my query. I'm trying to find the datediff(minutes) between two dates in the same column for an SSRS Report I'm making.

This is the result I want.

Date Difference
9/7/2012 9:59
9/7/2012 9:59 0
9/7/2012 12:41 162
9/7/2012 12:41 0
9/7/2012 13:05 24
9/8/2012 9:20 2015
9/8/2012 9:20 0

Happy to supply more information.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:33:43

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-11 : 16:28:45

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 16:58:07

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

Are you using SQL 2005 and above with compatibility 90 or above?

what does below return?


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-11 : 17:08:26

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-11 : 17:09:02

This gets me a lot closer to what I'm looking for. I should only have 7 results though and this is giving me 49.

SELECT DISTINCT
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, DATEDIFF(Minute,
COALESCE (Illusion_Notes_1.NoteDate, Illusion_Notes_1.NoteDate), Illusion_Notes.NoteDate) AS Difference, Illusion_Notes_1.NoteDate AS Note2,
Illusion_Notes.NoteID AS NoteID1, Illusion_Notes_1.NoteID AS NoteID2
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.TaskID = Illusion_Notes_1.TaskID
WHERE (Illusion_Staff.Username = 'cott') AND (DATEPART(m, Illusion_Task.DateOpened) = '9') 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_1.NoteDate, Illusion_Notes.NoteID, Illusion_Notes_1.NoteID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 23:48:51

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)

what about compatibility level?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 10:31:51

Oh sorry - it's 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:42:13

then rOW_NUMBER should work. unless you're using it in some other editor

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 14:48:14

Don't suppose you could fix it to where it works with my query above? I can't figure out how to plug in your example to make it work in my query. Like I said, I'm kinda new to doing queries. Here's what I have so far and I'm getting an error: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

;With Illusion_Notes.NoteDate
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Illusion_Notes.NoteDate) AS Seq,Illusion_Notes_1.NoteDate
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 JOIN
Illusion_Notes_1 on Illusion_Notes_1.seq=Illusion_Notes.Seq-1
)

SELECT 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, DATEDIFF(Minute,
COALESCE (Illusion_Notes_1.NoteDate, Illusion_Notes_1.NoteDate), Illusion_Notes.NoteDate) AS Difference, Illusion_Notes_1.NoteDate AS Note2,
Illusion_Notes.NoteID AS NoteID1, Illusion_Notes_1.NoteID AS NoteID2
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 JOIN
Illusion_Notes_1 on Illusion_Notes_1.seq=Illusion_Notes.Seq-1
WHERE (Illusion_Staff.Username = 'cott') AND (DATEPART(m, Illusion_Task.DateOpened) = '9') 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_1.NoteDate, Illusion_Notes.NoteID, Illusion_Notes_1.NoteID
ORDER BY Illusion_Notes.NoteDate DESC, NoteID1, Note2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:00:59

your group by has same column referenced again and again. can i ask reason for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 15:12:19

Just an oversight on my part. I've cleaned up the duplicates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:48:42

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 16:39:47

You are the F'ING MAN! Got it working. Thank you so MUCH!

One last question and I'll let you go. You see in the report my Where statement has specific information for Username and Technician and Ticket #

I'm trying to do this:

Where Illusion_Staff.Username = @Technician and have it allow you to choose the technician based on the results of another Dataset. Same for DATEPART(m, Illusion_Task.DateOpened) = @Month

When I run the query I get an error saying: Must declare the scalar variable "@Technician" or Must declare the scalar variable "@Month"

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 16:40:29

Here's the query

; 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 17:26:03

you need to declare and pass the values for variables if you want to use them in query


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 17:39:23

Is there a way without declaring? I ask because I'm using the Query Designer in BIDS and The Declare SQL construct or statement is not supported. :/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 17:45:36

oh...you 're using bids

then just use your original query without declare and click on execute button (! on top)
then it will prompt for values for Technician and Month. give sample values and run the query

upon completion and getting results, click refresh dataset button on to. it will automatically add parameters for you in the report through which you can pass values when you run the report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-12 : 17:55:11

That's what I'm saying though - normally that works. I put this in : Where Illusion_Staff.Username = @Technician and I get this message "Must declare the scalar variable "@Technician"

Normally it works like a champ. Maybe I have it in the wrong spot?

; 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_Task.SeverityID, 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 = @Tech AND DATEPART(m, Illusion_Task.DateOpened) = @Month
GROUP BY Illusion_Task.DateClosed, Illusion_Task.TaskID, Illusion_Staff.Username, Illusion_TaskType.TaskTypeDescription, Illusion_Task.SeverityID,
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:31:41

nope...it should work

if its not working wrap it in a stored procedure and make technician,month as parameters and call it from BIDS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jkuhn76
Starting Member

12 Posts

Posted - 2012-09-13 : 10:41:43

Got it working - thanks again for your help man. You're a life saver! Now I know where to come when I'm stuck in the future :)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources