How do I do this???

[tedi1]

Hi all,

I have this report I need to create which at the end will give the average time users spent on the site.
The problem is that there is no Logout in the log, so the only way to know the time spent on the site is by calculating the gap between the login time and the last action time prior to the next login.
The problem is finding the Login and the last action connected to it.
I have tried some ways but with no luck.
To help you help me I have copied some data out of the DB.
What I need to get is the gap between the raw with prod = 0 and the last raw where prod <> 0 and it's ID is smaller then the next login's (Prod = 0) Id.

I'm useing CR 9 with ASP on W2K


Id Comp User Prod date
------- ------- ------ ------ --------------------
2067957 1 18 0 04/12/2003 09:17:23
2067979 1 18 50 04/12/2003 09:19:11
2068014 1 18 2 04/12/2003 09:21:17
2068077 1 18 50 04/12/2003 09:25:49
2068118 1 18 50 04/12/2003 09:28:50
2071003 1 18 0 04/12/2003 13:33:58
2071013 1 18 50 04/12/2003 13:34:37
2071015 1 18 1 04/12/2003 13:34:52
2071031 1 18 53 04/12/2003 13:36:20

 

[Ngolem]

So in the this example the answer would be

4 hours 5 mins 8 secs

based on these 2 records

2068118 1 18 50 04/12/2003 09:28:50
2071003 1 18 0 04/12/2003 13:33:58

Is that what you are looking for? You should clearly illustrate the result with your sample data

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Here's an approach that will give you the average time used, although you would need to then translate this into the particular time format you want to display:

{@avecalc} for the details section:
whileprintingrecords;
datetimevar start;
datetimevar end;
numbervar sumendstartdiff;
numbervar counter;
numbervar ave;

if onfirstrecord or
{table.prod} = 0 then
start := {table.datetime};
if onlastrecord or
next({table.prod}) = 0 then
end := {table.datetime} else
end := datetime(0,0,0,0,0,0);
sumendstartdiff := sumendstartdiff + (end-start);
if onlastrecord or
next({table.prod}) = 0 then counter := counter + 1;
if counter <> 0 then
ave := sumendstartdiff/counter;

{@displave} for the group or report footer:
whileprintingrecords;
numbervar ave;

I couldn't see that the ID needed to play any role in the calculations, since in your example it appeared to always increase over time, so I didn't build this in. If you plan to group on user, then you also need to add a reset formula for the group header:

{@reset}:
whileprintingrecords;
numbervar sumendstartdiff := 0;
numbervar counter := 0;
numbervar ave := 0;

And change the detail level formula to:

whileprintingrecords;
datetimevar start;
datetimevar end;
numbervar sumendstartdiff;
numbervar counter;
numbervar ave;

if onfirstrecord or
{table.user} <> previous({table.user}) or
{table.prod} = 0 then
start := {table.datetime};
if onlastrecord or
next({table.prod}) = 0 or
{#cntwingrp} = count({table.user},{table.user}) then
end := {table.datetime} else
end := datetime(0,0,0,0,0,0);
sumendstartdiff := sumendstartdiff + (end-start);
if onlastrecord or
next({table.prod}) = 0 or
next({table.user}) <> {table.user}
then counter := counter + 1;
if counter <> 0 then ave :=
sumendstartdiff/counter;

//where {#cntwingrp} is a running total of {table.user}, count, evaluate for each record, reset on change of Group (User)

You would place {@displave} in the group footer.

-LB