July 5, 2012 at 4:18 am
I need help with SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so forth on Column Datatime..
I used Row_Number() Over on 2005 and 2008 and it works
Datatime Column 1Column 2Column 3
2012-03-03 14:20:35.000 1111222223334500511
2012-03-03 15:17:17.000 1111222223334500511
2012-03-03 15:26:13.000 1111222223334500511
2012-03-03 15:27:40.000 1111222223334500511
2012-03-03 15:33:28.000 1111222223334500511
2012-03-03 15:35:44.000 1111222223334500511
2012-03-03 15:38:55.000 1111222223334500511
2012-03-12 12:28:41.000 1111222223334500511
2012-03-12 12:31:00.000 1111222223334500511
2012-04-05 08:29:45.000 1111222223334500511
2012-04-05 08:32:40.000 1111222223334500511
2012-04-05 08:34:00.000 1111222223334500511
2012-03-15 14:18:05.000 7406140316086345441
2012-03-15 14:30:03.000 7406140316086345441
2012-02-24 14:41:24.000 BN422019304171
2012-02-24 15:03:26.000 BN422019304171
I need the query to check the difference and return results if difference is more than an hour else return 1 row
Expected results
Datatime Column 1 Column 2 Column 3
2012-03-03 14:20:35.000 1111222223334500511
2012-03-03 15:26:13.000 1111222223334500511
2012-03-12 12:28:41.000 1111222223334500511
2012-04-05 08:29:45.000 1111222223334500511
2012-03-15 14:18:05.000 7406140316086345441
2012-02-24 14:41:24.000 BN422019304171
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
July 5, 2012 at 4:28 am
this is the Tsql i was using
SELECT [Column 1], [Datatime],[Column 2] , [Column 3]
FROM [test].[dbo].[upload] S
WHERE NOT EXISTS (SELECT *
FROM [test].[dbo].[upload] x
WHERE x.[Column 2] = S.[Column 2]
and x.[Column 1] = S.[Column 1]
AND DATEDIFF(HH,S.Datatime,1) > 0
or (x.Datatime = S.Datatime AND
x.[Column 3]< S.[Column 3])
)
order by S.[Column 1]
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
July 5, 2012 at 5:21 am
The description's a bit vague so this is something of a guess:
SELECT [Column 1], [Datatime],[Column 2] , [Column 3]
FROM [test].[dbo].[upload] S
WHERE NOT EXISTS (
SELECT 1
FROM [test].[dbo].[upload] x
WHERE x.[Column 2] = S.[Column 2]
AND x.[Column 1] = S.[Column 1]
AND ABS(DATEDIFF(HH,x.Datatime,s.Datatime)) < 1
)
order by S.[Column 1]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2012 at 8:40 pm
kenneth.mofokeng (7/5/2012)
I need help with SQL 2000 Query to get time difference between 1st row and 2nd, 2nd and 3rd, 3rd and 4th and so forth on Column Datatime..I used Row_Number() Over on 2005 and 2008 and it works
OVER() is pretty much a different look for a good old table variable.
Try something like this:
DECLARE @TempTable TABLE (
N int IDENTITY(1,1) PRIMARY KEY,
[Datatime] ... ,
[Column 1] ... ,
[Column 2] ... ,
[Column 3] ... ,
)
INSERT INTO @TempTable
([Datatime], [Column 1], [Column 2], [Column 3])
SELECT [Datatime], [Column 1], [Column 2], [Column 3]
FROM YourTable
WHERE {define your range here}
order by [Datatime]
SELECT T2.DataTime - T1.DataTime as TimeDiff, T1.[Datatime], T1.[Column 1], T1.[Column 2], T1.[Column 3]
FROM @TempTable T1
INNER JOIN @TempTable T2 ON T2.N = T1.N + 1
WHERE T2.DataTime > DATEADD(hh, 1, T1.DataTime)
_____________
Code for TallyGenerator
August 10, 2012 at 2:48 am
ChrisM@Work Sergily , 🙂 Thank you guys very much, I used both your suggestions and got Desired results 😉
SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply