Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Unanswered: User Activity Query from a Log Table

    Hey!

    I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.

    So, I have a table like this:

    Code:
    ChangeID	UserID		LoggedIn	DateTime
    7		1		0		7/30/06 12:30PM
    6		1		1		7/30/06	12:20PM
    5		1		0		7/29/06 11:00AM
    4		1		1		7/29/06 10:30AM
    3		2		0		5/03/06 6:30PM
    2		2		1		5/03/06	5:30PM
    1		2		0		5/01/06 9:30AM
    0		2		1		5/01/06 9:15AM
    And I want to make it into this table:

    Code:
    UserID		LoggedInDate		LoggedOutDate		Duration 
    1		7/29/06 10:30AM		7/29/06 11:00AM		30
    1		7/30/06 12:20PM		7/30/06 12:30PM		10
    2		5/01/06 9:15AM		5/01/06 9:30AM		15
    2		5/03/06	5:30PM		5/03/06 6:30PM		60

    Here is what I came up with, but it doesn't seem to be working right

    Code:
    select 
    	distinct (userin.changeid),
    	userin.userid,
    	userin.createdate as LoggedInDate,
    	userout.createdate as LoggedOutDate,
    	DateDiff(mm,userin.createdate,userout.createdate) as Duration
    from userChange userin
    	inner join (
    		select * from userChange 
    		where loggedin = 0
    	) userout on userin.userid = userout.userid
    where  and userin.loggedin = 1 and userin.createdate < userout.createdate
    order by userin.createdate desc
    Last edited by fivekilobytes; 01-07-07 at 15:46.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it's easier for those that want to help if you provide DDL for the tables, and DML for getting the sample data in.

    I'd take a stab at it if you provided that - I'm too lazy to write the insert statements myself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •