Calculate Internet Usage based on rows

[gillonia]

CR10

From a table containing the following two fields:

"URL", "DateTime"

I'm looking to calculate the total time spent on internet sites, data is in the following format:

<Row 1>

http://www.bbc.co.uk

01/08/2008 12:40
<Row 2 >

http://www.bbc.co.uk

01/08/2008 12:42
<Row 3 >

http://www.bbc.co.uk

01/08/2008 12:43
<Row 4 >

http://www.bbc.co.uk

01/08/2008 12:45
<Row 5 >

http://www.bbc.co.uk

01/08/2008 12:48
<Row 6 >

http://www.bbc.co.uk

01/08/2008 12:49
<Row 7 >

http://www.edinburghnews.com

01/01/2008 15:23
<Row 8 >

http://www.edinburghnews.com

01/01/2008 15:25
<Row 9 >

http://www.edinburghnews.com

01/01/2008 15:31
<Row 10 >

http://www.edinburghnews.com

01/01/2008 15:32
<Row 11 >

http://www.edinburghnews.com

01/01/2008 15:34
<Row 12 >

http://www.edinburghnews.com

01/01/2008 15:37

I would like a summary of:

http://www.bbc.co.uk

9 mins

http://www.edinburghnews.com

14 mins
IE grouped by URL and time compared to previous row.

Any suggestions on how to achieve this would be most appreciated, thanks in advance.

 

[raja2008]

group by the site and then
have a formula to get the max-min of the time spent.

Have one summary with Maximum and the other with Minimum and then write a formula to subtract.

I hope this helps.

 

[gillonia]

Thanks very much for the reply Raj, one issue I foresee with that is if the pattern repeated itself later in the day, ie in the example below, if I take the max & min of

http://www.bbc.co.uk

it'll give me 12:40-15:49, when I should really be looking at 12:40-12:49 & 15:40-15:49....

<Row 1>

http://www.bbc.co.uk

01/08/2008 12:40
<Row 2 >

http://www.bbc.co.uk

01/08/2008 12:42
<Row 3 >

http://www.bbc.co.uk

01/08/2008 12:43
<Row 4 >

http://www.bbc.co.uk

01/08/2008 12:45
<Row 5 >

http://www.bbc.co.uk

01/08/2008 12:48
<Row 6 >

http://www.bbc.co.uk

01/08/2008 12:49
<Row 7 >

http://www.edinburghnews.com

01/01/2008 15:23
<Row 8 >

http://www.edinburghnews.com

01/01/2008 15:25
<Row 9 >

http://www.edinburghnews.com

01/01/2008 15:31
<Row 10 >

http://www.edinburghnews.com

01/01/2008 15:32
<Row 11 >

http://www.edinburghnews.com

01/01/2008 15:34
<Row 12 >

http://www.edinburghnews.com

01/01/2008 15:37
<Row 13>

http://www.bbc.co.uk

01/08/2008 15:40
<Row 14 >

http://www.bbc.co.uk

01/08/2008 15:42
<Row 15 >

http://www.bbc.co.uk

01/08/2008 15:43
<Row 16 >

http://www.bbc.co.uk

01/08/2008 15:45
<Row 17 >

http://www.bbc.co.uk

01/08/2008 15:48
<Row 18 >

http://www.bbc.co.uk

01/08/2008 15:49

What do you think?

 

[raja2008]

Just a idea not tested

Here is waht i might give it a try:
Group on the url

Have a formula to get the diff of the two consecutive rows.
Diff:
if firstrecord or table.url<>previous(table.url)
then diff=0 else
table.url-previous(table.url)

place this in the details section,then have a running total to add these up .

Will be back,didn't put it into exact tech terms ,just put a thought here.
Hope this gives an idea .

As you know running total reset after group.

 

[raja2008]

Group on URL then create a Formula time spent:
whileprintingrecords;
numbervar timespent;

If PreviousIsNull ({table.url}) or {table.url}<>previous( {table.url}) Then timespent=0 else datediff("s",{table.url},previous({table.url})

What basically i'm trying to do is see if it's the first record or if it's the first record of the next batch of the same url and making the timespent 0.
else the differrence. This will give you the minutes.

Now you can have a summary on this formula at group level to get the total minutes .
Then you can change the minutes into HH:MM:SS by changing this formula a little bit.
FAQ767-3543: Displaying a time as HH:MM:SS or DD:HH:MM:SS


I still didn't tested it ,i will try to test it.

 

[gillonia]

Thanks raja2008, I'll give this a try.