Best way to do this.....
-
Hi all,
Having this table TB_LOG
ID ServerTime User
1 2004-03-18 14:44:30.000 A
2 2004-03-18 14:44:31.000 A
3 2004-04-30 08:28:06.000 B
4 2004-04-30 08:29:19.000 B
5 2004-04-30 08:30:46.000 B
6 2004-04-30 08:30:47.000 B
7 2004-04-30 08:30:58.000 B
8 2004-04-30 14:54:28.000 A
9 2004-04-30 14:54:29.000 A
10 2004-04-30 14:54:37.000 A
11 2004-05-13 16:54:58.000 C
12 2004-05-18 12:29:42.000 C
13 2004-05-18 12:29:43.000 C
14 2004-05-18 12:35:41.000 B
15 2004-05-18 16:55:54.000 B
16 2004-05-18 20:55:27.000 C
and so on.....
what is the best way to calculate (compute) amout of time for each user ?
ServerTime is date of some operation of the user...
the key is found min and max for each "group". A "group" starts when the User changes and the group ends
just before the User changes again
something like
this
select T1.[User],
MIN(T1.ServerTime) as StartTIme ,
MAX(T2.ServerTime) as EndTime
from TB_LOG as T1
join TB_LOG as T2
on T1.[User] = T2.[User] AND
T1.ServerTime >= T2.ServerTime
group by T1.[User]
but this works only if i-user works only one time.
....otherwisegrouping set alter min and max values......
Any suggestion?
Thanks in advance
-
Is this same group
11 2004-05-13 16:54:58.000 C
12 2004-05-18 12:29:42.000 C
13 2004-05-18 12:29:43.000 C
Even if date has changed?
Could you also post an example of the output you want from the supplied data?
N 56°04'39.16"
E 12°55'05.25" -
Yes, tha main goal is discovering group ie
For user A
MIN is row 1 2004-03-18 14:44:30.000 A
MAX is row 2 2004-03-18 14:44:31.000 A
make group ga1
MIN is row 8 2004-04-30 14:54:28.000 A
MAX is row 10 2004-04-30 14:54:37.000 A
make group ga2
For user B
MIN is row 3 2004-04-30 08:28:06.000 B
MAX is row 7 2004-04-30 08:30:58.000 B
make group gb1
MIN is row 14 2004-05-18 12:35:41.000 B
MAX is row 15 2004-05-18 16:55:54.000 B
make group gb2
For User C
MIN is row 11 2004-05-13 16:54:58.000 C
MAX is row 13 2004-05-18 12:29:43.000 C
make group gc1
note: row 16 2004-05-18 20:55:27.000 C, should be considered only with other data
( If we have more rows for C) so we discard it.....
then, i can compute datediff for each component (ga1+ga2 etc..).....so we obtain the total time
for the user A, B and C.
-
Something like this?
In this example, IDs must be consecutive, otherwise just create a table variable with ID as IDENTITY and insert into that table first, ordered by original ID. This way, the gaps in the original IDs are gone but still in the same order.
-- Populate test data
DECLARE @t TABLE (ID INT, ServerTime DATETIME, [User] CHAR(1))
INSERT @t
SELECT 1, '2004-03-18 14:44:30.000', 'A' UNION ALL
SELECT 2, '2004-03-18 14:44:31.000', 'A' UNION ALL
SELECT 3, '2004-04-30 08:28:06.000', 'B' UNION ALL
SELECT 4, '2004-04-30 08:29:19.000', 'B' UNION ALL
SELECT 5, '2004-04-30 08:30:46.000', 'B' UNION ALL
SELECT 6, '2004-04-30 08:30:47.000', 'B' UNION ALL
SELECT 7, '2004-04-30 08:30:58.000', 'B' UNION ALL
SELECT 8, '2004-04-30 14:54:28.000', 'A' UNION ALL
SELECT 9, '2004-04-30 14:54:29.000', 'A' UNION ALL
SELECT 10, '2004-04-30 14:54:37.000', 'A' UNION ALL
SELECT 11, '2004-05-13 16:54:58.000', 'C' UNION ALL
SELECT 12, '2004-05-18 12:29:42.000', 'C' UNION ALL
SELECT 13, '2004-05-18 12:29:43.000', 'C' UNION ALL
SELECT 14, '2004-05-18 12:35:41.000', 'B' UNION ALL
SELECT 15, '2004-05-18 16:55:54.000', 'B' UNION ALL
SELECT 16, '2004-05-18 20:55:27.000', 'C'
-- Do the magic!
SELECT z.[User],
tFrom.ServerTime FromTime,
tTo.ServerTime ToTime
FROM (
SELECT LowLimit.[User],
LowLimit.ID FromID,
ISNULL((
SELECT TOP 1 ID - 1
FROM @t HighLimit
WHERE NOT EXISTS (
SELECT B.ID
FROM @t B
WHERE HighLimit.ID + 1 = B.ID
AND HighLimit.[User] = LowLimit.[User]
)
AND HighLimit.ID > LowLimit.ID
ORDER BY ID
), LowLimit.ID) ToID
FROM @t LowLimit
WHERE NOT EXISTS (
SELECT B.ID
FROM @t B
WHERE LowLimit.ID - 1 = B.ID
AND B.[User] = LowLimit.[User]
)
) z
INNER JOIN @t tFrom ON tFrom.ID = z.FromID
INNER JOIN @t tTo ON tTo.ID = z.ToID
OUTPUT
User FomTime ToTime
---- ---------------------------------- ----------------------------------
A 2004-03-18 14:44:30.000 2004-03-18 14:44:31.000
B 2004-04-30 08:28:06.000 2004-04-30 08:30:58.000
A 2004-04-30 14:54:28.000 2004-04-30 14:54:37.000
C 2004-05-13 16:54:58.000 2004-05-18 12:29:43.000
B 2004-05-18 12:35:41.000 2004-05-18 16:55:54.000
C 2004-05-18 20:55:27.000 2004-05-18 20:55:27.000
You probably would want to add
WHERE tFrom.ID <> tTo.ID
ORDER BY z.[User],
tFrom.ServerTime
to the end of the query...
OUTPUT
User FromTime ToTime
---- ---------------------------------- ----------------------------------
A 2004-03-18 14:44:30.000 2004-03-18 14:44:31.000
A 2004-04-30 14:54:28.000 2004-04-30 14:54:37.000
B 2004-04-30 08:28:06.000 2004-04-30 08:30:58.000
B 2004-05-18 12:35:41.000 2004-05-18 16:55:54.000
C 2004-05-13 16:54:58.000 2004-05-18 12:29:43.000
N 56°04'39.16"
E 12°55'05.25" -
very very Thanks i'll try to understand the magic!!!! (i think to take long time :blush
-
Hi Peter.....
I post here a more simple solution, (Thanks HUGO) if someone is interested...
it uses the simple arithmetic fact that
DateDiff (row10,8) equals DateDiff (row10,9) + DateDiff (row9,8)
SELECT a.Usr, SUM(DATEDIFF(second, a.ServerTime, b.ServerTime))
FROM TB_LOG AS a
INNER JOIN TB_LOG AS b
ON b.Usr = a.Usr
AND b.ServerTime = (SELECT MIN(c.ServerTime)
FROM TB_LOG AS c
WHERE c.ServerTime > a.ServerTime)
GROUP BY a.Usr
-
Nice. Well done!
I was so focused on producing the groups, I didn't see the obvious.
N 56°04'39.16"
E 12°55'05.25"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply