Duration a user was logged in

[dandanearl]

Hello!
I'm sitting here in cleveland looking at the sun, which I haven't seen in 6 months, trying to wrap my head around this DateDiff problem. What Im trying to say is that I'm checked out, but need to finish a project!

I am looking to grab the difference in time of when a user logs in and then out for a certain period time.
example table below

securitylognum|usernum|registernum|logdate |messagetext|actionnum|subactionnum
1 105 106 5/16/13.11:10:24:2 logon 1 1
2 105 106 5/16/13.24:15:13:3 logoff 3 1

So, when a user logs in, the system creates a logdate, actionnum 1 and subactionnum 1. When the users log off logdate, actionnum 3 and subactionnum 1. What I'm struggling with is how to capture the next appropriate securitylognum to get the DateDiff.

using crystal 2008
Any help would be great. thanks.

 

[IdoMillet]

Bring in the same table as an alias into the report.
Restrict the 1st instance to actionnum of 1 (logon)
Restrict the 2nd instance to actionnum of 3 (logoff)
Join on usernum, and on subaction (assuming the subaction code tells the system that this is the same "transaction).

Then, you can simply compute the difference between the DateTime in the Logoff Alias and the DateTime in the Logon alias.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[dandanearl]

I was on the vendors customer blog and found the sql, but when I put it in as a command. The duration repeats(for ex. user logs in twice for date range but shows four). When run in SQL management studio user comes up twice.

SELECT u.username AS "User Name", b.logdate AS "Login Date", a.logdate AS "Logout Date", datediff(hh,b.logdate,a.logdate) AS "Duration(Hours)"  from securitylog a,
        (select securitylognum, usernum, logdate, actionnum
        from securitylog where actionnum = 1 and subactionnum=1) b,
        useraccount u
        where a.usernum = b.usernum
        and u.usernum = a.usernum
        and a.actionnum = 3
        and a.subactionnum = 1
        and datediff(hh,b.logdate,a.logdate) > 10
        and a.logdate >= DATEADD(m, - 1 + DATEDIFF(m, '19000101', GETDATE()), '19000101')
        and b.securitylognum = (select max(securitylognum)
        from securitylog
        where securitylognum < a.securitylognum
        and usernum = a.usernum and actionnum = 1
        and subactionnum = 1)
        order by  b.logdate desc

How would I manually do this using field explorer?
[ol ]
[li]select securitylog twice(create alias table)and useraccount tables.[/li]
[li]Create multiple formuals; logon, logout, duration, etc...based on sql above.[/li]
[/ol]
As you can probably tell...I am by no means a developer(unless you consider copying and pasting developing)!

Any pointers are appreciated!