Logon / Logoff Time Difference

[Viscount]

Hi

CR XI / Access 2003

I am currently trying to create a formula that will work out the duration that a user has been logged in to a system.

The data looks like...

User ID Action Date/Time
1 Logon 01/01/2006 07:30
2 Logon 01/01/2006 07.38
1 Call 01/01/2006 08:01
1 EndCall 01/01/2006 08:09
2 Call 01/01/2006 08.15
1 Logon 01/01/2006 08.18
2 EndCall 01/01/2006 08:20
1 Call 01/01/2006 08:33
1 EndCall 01/01/2006 08:36
2 Logof 01/01/2006 08:46
etc...

2 Logof 01/01/2006 16:28
1 Logof 01/01/2006 16:37

What I want to know is the time difference between the 1st logon and the last logof for each user per day

I am happy grouping by User and then by day, but I get stuck when trying to work out the duration.

Any help greately received!

Thanks

Vis

 

[lbass]

If the first record of the day is always logon and the last record is always logoff, then use a formula like this:

datediff("s",minimum({table.datetime},{table.user}), maximum({table.datetime},{table.user}))

This assumes you have a group on user. Then you can convert the seconds into a string using the hh:mm:ss format, if you wish. There's an FAQ on how to do this.

-LB

 

[Viscount]

Thanks for your reply - unfortunately for some reason the first and last record are not always Logon / Logoff - my example is not 100% accruate.

Any further ideas..?

Thanks

Vis

 

[Madawc]

Do a pair of running totals. One to find the minimum date/time when Action is 'Logon'. The other to find the maximum date/time when Action is 'Logof'.

Find the date difference between the two, as lbass detailed.

I'd advise first displaying the running totals and then finding the difference. Even if you later remove them, it's good to see the 'workfields' and be sure that they produce the right result.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Viscount]

Thanks for that - I think that it has got it!

The only bizarre thing is that one user only worked for 7 seconds on one particular day - but knowing him, that is probably right!!!!

Thanks for your help Mad & LBass

 

[lbass]

You could use conditional formulas for this:

//{@Logon}:
if {table.action} = "Logon" then {table.datetime} else datetime(9999,09,09,0,0,0)

//{@Logoff}:
if {table.action} = "Logof" then {table.datetime} else datetime(0,0,0,0,0,0)

Then do the datediff:
datediff("s",minimum({@Logon}},{table.user}), maximum({@Logoff},{table.user}))

-LB