January 10, 2007 at 9:15 pm
Hi,
I'm trying to do something tricky and I was wondering if it's possible to do it in a single update statement without using cursors.
Here's the thing:
Let's say we have this table
date userId TimeSpan
--------------------------
1:00 1
1:01 2
1:03 1
1:10 2
1:15 1
What I need to do is to calculate the time between a log and the the next after him (for the same user of course). So, for the user #1, between the first and the second log, there has been 3 minutes. 9 minutes for the user #2. 12 minutes between the log the second and the third log for the user #1.
So after the update, the table would look like this:
date userId TimeSpan
--------------------------
1:00 1 3
1:01 2 9
1:03 1 12
1:10 2
1:15 1
The two last log don't have timeSpan because those are the last log for a user.
I'm not sure if I made myself clear enough, but if someone has an idea of who to write this kind of update, please let me know!
Thanks
Stephane
January 11, 2007 at 12:44 am
This is a hurried reply, no warranties implied...
Assumptions:
1. table name is dtTable
2. Time spans are in minutes
UPDATE dtTable
SET TimeSpan =
DATEDIFF(minute,t1.dt,(SELECT MIN(t2.dt)
FROM dtTable t2
WHERE [t2].[id] = [t1].[id]
AND [T2].dt > t1.dt))
FROM dtTable t1
January 11, 2007 at 8:12 am
I found the solution:
CREATE TABLE #logs ([date] DATETIME NOT NULL,
userId INT NOT NULL)
INSERT INTO #logs ([date], userId)
VALUES ('1:00', 1)
INSERT INTO #logs ([date], userId)
VALUES ('1:01', 2)
INSERT INTO #logs ([date], userId)
VALUES ('1:03', 1)
INSERT INTO #logs ([date], userId)
VALUES ('1:10', 2)
INSERT INTO #logs ([date], userId)
VALUES ('1:15', 1)
SELECT l1.date, l1.userId, DATEDIFF(minute, l1.[date], l2.[date]) AS
timeSpan
FROM #logs l1
LEFT JOIN #logs l2
ON l1.userId = l2.userId
AND l1.[date] = (
SELECT MAX([date])
FROM #logs l3
WHERE l3.[date] < l2.[date]
AND l3.userId = l2.userId
)
ORDER BY l1.userId, l1.[date]
DROP TABLE #logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply