Selecting the interval for datetime 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 2005 Forums

Transact-SQL (2005)

Selecting the interval for datetime column

Author

Topic

fakepoo
Starting Member

7 Posts

Posted - 2009-11-11 : 14:03:10

I have a table called Meters that has a column called DateLastUpdated. I would like a select command that will return the interval of time that has occurred between each Meters record and the previous record. Is there a way to do that?

Thanks,
-fakepoo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-11 : 14:21:20

There are a bunch of ways to do that. Here are two way:

-- Sample Data
DECLARE @Meter TABLE(MeterID INT, DateLastUpdated DATETIME)

INSERT @Meter
SELECT 1, GETDATE()
UNION ALL SELECT 1, GETDATE() -2
UNION ALL SELECT 1, GETDATE () + 1
UNION ALL SELECT 2, GETDATE() - 5
UNION ALL SELECT 2, GETDATE() - 2
UNION ALL SELECT 2, GETDATE() + 1

-- Method 1
SELECT
	M.MeterID,
	M.DateLastUpdated,
	T.Interval
FROM
	@Meter AS M
CROSS APPLY
(
	SELECT COALESCE(DATEDIFF(SECOND, MAX(A.DateLastUpdated), M.DateLastUpdated), 0) AS Interval
	FROM @Meter AS A
	WHERE A.DateLastUpdated < M.DateLastUpdated
	AND M.MeterID = A.MeterID
) AS T
ORDER BY 
	M.MeterID, 
	M.DateLastUpdated


-- Method 2
SELECT 
	A.MeterID,
	A.DateLastUpdated,
	COALESCE(DATEDIFF(SECOND, B.DateLastUpdated, A.DateLastUpdated), 0) AS Interval
FROM
	(
		SELECT 
			MeterID, 
			DateLastUpdated,
			ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY DateLastUpdated) AS RowNum
		FROM 
			@Meter
	) AS A
LEFT OUTER JOIN
	(
		SELECT 
			MeterID, 
			DateLastUpdated,
			ROW_NUMBER() OVER (PARTITION BY MeterID ORDER BY DateLastUpdated) AS RowNum
		FROM 
			@Meter
	) AS B
	ON A.MeterID = B.MeterID
	AND A.RowNum = B.RowNum + 1
ORDER BY
	A.MeterID,
	A.DateLastUpdated

fakepoo
Starting Member

7 Posts

Posted - 2009-11-11 : 14:53:58

Thank you so much!

Is there a way to return an interval of zero if there is not a previous date (the first meter)?

Instead, I am getting a 1558561.

Thanks,
-fakepoo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-11 : 15:57:34

I'm not sure how you implemented the sample I provided. But, the sample does return a zero if there is not a previous date.

fakepoo
Starting Member

7 Posts

Posted - 2009-11-12 : 09:28:25

Here is my query. To get around that problem, I put a clause in the WHERE section so that the interval must be less than 30000 although it would be better to not have to do that.


-- Time Stamp Interval
SELECT 
	A.RouteID, 
	A.AccountID, 
	A.Address,
	A.SequenceNumber,
	M.MeterID, 
	CASE WHEN M.Reading1 IS NULL THEN NULL ELSE CONVERT(decimal(20, 4), M.Reading1) END AS Reading1, 
	CASE WHEN M.Reading2 IS NULL THEN NULL ELSE CONVERT(decimal(20, 4), M.Reading2) END AS Reading2, 
	M.Comment1, 
	M.Comment2, 
	M.Note, 
	CONVERT(DATETIME, SUBSTRING(M.DateLastUpdated,1,8) + ' ' + SUBSTRING(M.DateLastUpdated,9,2) + ':' + SUBSTRING(M.DateLastUpdated,11,2) + ':' + SUBSTRING(M.DateLastUpdated,13,2)) AS DateLastUpdated,
	CONVERT(NVARCHAR(8), DateAdd(Second, T.Interval, 0), 108) AS Interval,
	1 AS MinInterval
FROM Accounts AS A LEFT JOIN Meters AS M ON A.AccountID = M.AccountID

CROSS APPLY
(
	SELECT COALESCE(DATEDIFF(SECOND, MAX(CONVERT(DATETIME, SUBSTRING(M2.DateLastUpdated,1,8) + ' ' + SUBSTRING(M2.DateLastUpdated,9,2) + ':' + SUBSTRING(M2.DateLastUpdated,11,2) + ':' + SUBSTRING(M2.DateLastUpdated,13,2))), CONVERT(DATETIME, SUBSTRING(M.DateLastUpdated,1,8) + ' ' + SUBSTRING(M.DateLastUpdated,9,2) + ':' + SUBSTRING(M.DateLastUpdated,11,2) + ':' + SUBSTRING(M.DateLastUpdated,13,2))), 0) AS Interval
	FROM Meters AS M2
	WHERE M2.DateLastUpdated < M.DateLastUpdated
) 
AS T

WHERE A.RouteID = 'ROUTE0002' AND M.DateLastUpdated IS NOT NULL AND T.Interval >= 1 * 60 AND T.Interval < 30000
GROUP BY 
	A.RouteID, 
	A.AccountID, 
	A.Address,
	A.SequenceNumber,
	M.MeterID, 
	M.Reading1, 
	M.Reading2, 
	M.Comment1, 
	M.Comment2, 
	M.Note, 
	M.DateLastUpdated,
	T.Interval
ORDER BY M.DateLastUpdated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources