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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy