Help on grouping time

Hi all,
Having this table
ID ServerTime User
1 2004-05-18 14:44:30.000 A
2 2004-05-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
what is the best way to calculate (compute) amout of time for each user
(without cursor)?
ie
A has DateDiff(row2,1) + DateDiff(row10,8)
B has DateDiff(row7,3) + DateDiff(row15,14)
and so on...
Any suggestion'?
Thanks in advanceHow about :
SELECT User, dateidiff(day,min(ServerTime), max(ServerTime)
FROM ThisTable
GROUP BY User
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 08:13:47 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Hi all,
>Having this table
>ID ServerTime User
>1 2004-05-18 14:44:30.000 A
>2 2004-05-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
>
>what is the best way to calculate (compute) amout of time for each user
>(without cursor)?
>ie
>A has DateDiff(row2,1) + DateDiff(row10,8)
>B has DateDiff(row7,3) + DateDiff(row15,14)
>and so on...
>Any suggestion'?
>Thanks in advance|||Try this
declare @.mytable table(ID int,
ServerTime datetime,
[User] char(1))
insert into @.mytable(ID,ServerTime,[User])
select 1 ,'20040518 14:44:30.000','A' union all
select 2 ,'20040518 14:44:31.000','A' union all
select 3 ,'20040430 08:28:06.000','B' union all
select 4 ,'20040430 08:29:19.000','B' union all
select 5 ,'20040430 08:30:46.000','B' union all
select 6 ,'20040430 08:30:47.000','B' union all
select 7 ,'20040430 08:30:58.000','B' union all
select 8 ,'20040430 14:54:28.000','A' union all
select 9 ,'20040430 14:54:29.000','A' union all
select 10 ,'20040430 14:54:37.000','A' union all
select 11 ,'20040513 16:54:58.000','C' union all
select 12 ,'20040518 12:29:42.000','C' union all
select 13 ,'20040518 12:29:43.000','C' union all
select 14 ,'20040518 12:35:41.000','B' union all
select 15 ,'20040518 16:55:54.000','B' union all
select 16 ,'20040518 20:55:27.000','C'
select sum(Seconds),[User]
from (
select datediff(second,
min(ServerTime),
max(ServerTime)) as Seconds,
[User]
from (
select
t1.ServerTime,
t1.[User],
t1.ID-(select count(*)
from @.mytable t2
where t2.[User]=t1.[User]
and t2.ID<=t1.ID) as Rn
from @.mytable t1) X1
group by [User],Rn) X2
group by [User]|||thanks for your response Roy but:
I have some dubt ...ie
grouping make sets for A, B and C...then calculate min and max for
each group
so for A datediff works on rows 10 and 1 (max and min for A) but the
result it is not correct......
Better:
A has DateDiff(row10,1) (with your select)
but in general different from
A has DateDiff(row2,1) + DateDiff(row10,8)|||I think I might have a glimmer of what you are asking for now.
First, you are making pairs of rows, and calculating a difference in
time for each pair. No row is ignored.
Then, based on the results from all the pairs matching a given User,
you want to add up those results.
The difficulty is coming up with the pairings. All I can see is that
the "first" is the first in a pair, the "next" is the next, and then
the "next after that" is another "first", etc. There is nothing on
the rows to identify their position in the pairings, and a missing row
will throw everything very far off.
For a while I thought the following returned what you expected, though
it may not be performaing the datediff calculation in the direction or
at the precision you want. But on closer examination of your test
data, and the pairings you made manually, I no longer have any faith
that I understand the rules for pairing.
CREATE TABLE ThisTable
(ID tinyint, ServerTime datetime, Usr char(1))
GO
INSERT ThisTable values(1, '2004-05-18 14:44:30.000', 'A')
INSERT ThisTable values(2, '2004-05-18 14:44:31.000', 'A')
INSERT ThisTable values(3, '2004-04-30 08:28:06.000', 'B')
INSERT ThisTable values(4, '2004-04-30 08:29:19.000', 'B')
INSERT ThisTable values(5, '2004-04-30 08:30:46.000', 'B')
INSERT ThisTable values(6, '2004-04-30 08:30:47.000', 'B')
INSERT ThisTable values(7, '2004-04-30 08:30:58.000', 'B')
INSERT ThisTable values(8, '2004-04-30 14:54:28.000', 'A')
INSERT ThisTable values(9, '2004-04-30 14:54:29.000', 'A')
INSERT ThisTable values(10, '2004-04-30 14:54:37.000', 'A')
INSERT ThisTable values(11, '2004-05-13 16:54:58.000', 'C')
INSERT ThisTable values(12, '2004-05-18 12:29:42.000', 'C')
INSERT ThisTable values(13, '2004-05-18 12:29:43.000', 'C')
INSERT ThisTable values(14, '2004-05-18 12:35:41.000', 'B')
INSERT ThisTable values(15, '2004-05-18 16:55:54.000', 'B')
INSERT ThisTable values(16, '2004-05-18 20:55:27.000', 'C')
SELECT Usr, sum(datediff(second,StartTime,EndTime))
FROM (select T1.Usr,
T1.ServerTime as EndTime,
MAX(T2.ServerTime) as StartTIme
from ThisTable as T1
join ThisTable as T2
on T1.Usr = T2.Usr
and T1.ServerTime > T2.ServerTime
group by T1.Usr, T1.ServerTime
having count(*) % 2 = 1) as X
GROUP BY Usr
ORDER BY 1
Lets just look at the data for A. From the test data supplied:
id Usr ServerTime
-- -- --
1 A 2004-05-18 14:44:30.000
2 A 2004-05-18 14:44:31.000
8 A 2004-04-30 14:54:28.000
9 A 2004-04-30 14:54:29.000
10 A 2004-04-30 14:54:37.000
But that is ordered by id. I made the assumption that we should be
ordering by datetime:
id Usr ServerTime
-- -- --
8 A 2004-04-30 14:54:28.000
9 A 2004-04-30 14:54:29.000
10 A 2004-04-30 14:54:37.000
1 A 2004-05-18 14:44:30.000
2 A 2004-05-18 14:44:31.000
By THAT rule I was pairing the rows quite differently from what you
requested.
On a large table performance will be horrible. This is because the
inner query must do a "half-cartesian" join within each user; as the
number of rows per user increases, the work involved will get worse at
the rate of half the square of the number of rows.
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 09:11:19 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>thanks for your response Roy but:
>I have some dubt ...ie
>grouping make sets for A, B and C...then calculate min and max for
>each group
>so for A datediff works on rows 10 and 1 (max and min for A) but the
>result it is not correct......
>Better:
>A has DateDiff(row10,1) (with your select)
>but in general different from
>A has DateDiff(row2,1) + DateDiff(row10,8)|||If ID 4 had a ServerTime of 2004-04-30 08:27 (putting it earlier than ID 3),
would that alter your desired results? In other words, it looks like you're
doing calculations on contiguous rows of a user, but contiguous based on ID
order. Without more understanding of what you're doing, it would seem more
meaningful if the contiguous groups were based on ServerTime order, not ID.
Vern Rabe
"2006 Flauzer" wrote:

> Hi all,
> Having this table
> ID ServerTime User
> 1 2004-05-18 14:44:30.000 A
> 2 2004-05-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
>
> what is the best way to calculate (compute) amout of time for each user
> (without cursor)?
> ie
> A has DateDiff(row2,1) + DateDiff(row10,8)
> B has DateDiff(row7,3) + DateDiff(row15,14)
> and so on...
> Any suggestion'?
> Thanks in advance
>|||Excuse me if I have not been very clear but over the difficulty of the
question the English is not my first language (..it should be enough
clear :)))
The context of the question is analyze a "log" table:
ServerTime and User are the most important fields and substantially the
table records the
activities that every user make:First, we have "activities" for user A,
then user B and so on...
Therefore, there can be n rows for the user A m rows for the user B
etc... etc..
and these activities are adjoining and repeated (we not known how many
rows
As example, the working sequence could be A--C and then again =C1-B
etc...
The most obvious question is: can I count (with all the limitations of
the case) how much time to debit for the user =C1, how much for user B
and so on...
I looked for the best way to do this, and I've heard that "cursor" is
not a good way...
If someone has some suggestion=20
thanks in advance....|||If the ONLY activities that are recorded are the start and end of
whatever it is you are measuring, then something along the lines of
what I already posted can work.
If there is more information on the log that can be used to identify
the start and end of whatever it is you are charging for, then there
is probably a simpler (and more efficient) answer than what I posted.
Roy Harvey
Beacon Falls, CT
If there are other types of a
On 7 Jun 2006 13:06:18 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Excuse me if I have not been very clear but over the difficulty of the
>question the English is not my first language (..it should be enough
>clear :)))
>The context of the question is analyze a "log" table:
>ServerTime and User are the most important fields and substantially the
>table records the
>activities that every user make:First, we have "activities" for user A,
>then user B and so on...
>Therefore, there can be n rows for the user A m rows for the user B
>etc... etc..
>and these activities are adjoining and repeated (we not known how many
>rows
>As example, the working sequence could be A--C and then again -B
>etc...
>The most obvious question is: can I count (with all the limitations of
>the case) how much time to debit for the user , how much for user B
>and so on...
>I looked for the best way to do this, and I've heard that "cursor" is
>not a good way...
>If someone has some suggestion
>thanks in advance....|||Yes, Roy you are right...
Unfortunately i have no other useful info......
discovering min and max of every "group" is the key... 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....otherwise
grouping set alter min and max values.....
Thanks for your useful thoughts|||If the times are what you have to work with, look at the query I
posted earlier.
Roy
On 7 Jun 2006 14:18:19 -0700, "2006 Flauzer" <Flauzer@.libero.it>
wrote:

>Yes, Roy you are right...
>Unfortunately i have no other useful info......
>discovering min and max of every "group" is the key... 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....otherwise
>grouping set alter min and max values.....
>Thanks for your useful thoughts