SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculate TOTAL login/Logout times
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murd
Starting Member

23 Posts

Posted - 11/22/2013 :  11:09:02  Show Profile  Reply with Quote
Having some difficulties coming up with the most accurate way of calculating total login/logout times.

Structure:

	CREATE TABLE dbo.AUDIT_EVENT(
     EVENT_ID            numeric(19, 0)     NOT NULL,
     PROFILE_ID          numeric(19, 0)     NULL,
     EVENT_TIMESTAMP     datetime           NULL,
     EVENT_TYPE_ID       numeric(19, 0)     NULL,
     PID                 numeric(19, 0)     NULL,
     SDID                numeric(19, 0)     NULL,
     USER_ROLE_ID        numeric(19, 0)     NULL,
     PIDLINK             numeric(19, 0)     NULL,
     OUTCOME             smallint           NULL,
     EXPORTED            varchar(1)         CONSTRAINT [DF__AUDIT_EVENT__EXPORTED] DEFAULT 'N' NULL,
     UserIsRequestor     bit                NULL,
     CONSTRAINT PK_AUDIT_EVENT PRIMARY KEY CLUSTERED (EVENT_ID),
     CONSTRAINT RefPatientProfile356 FOREIGN KEY (PID)
     REFERENCES dbo.PatientProfile(PID),
     CONSTRAINT FK_AUDIT_EVENT_AUDIT_EVENT_TYPE FOREIGN KEY (EVENT_TYPE_ID)
     REFERENCES dbo.AUDIT_EVENT_TYPE(EVENT_TYPE_ID) ON UPDATE CASCADE,
     CONSTRAINT FK_AUDIT_EVENT_AUDIT_PROFILE FOREIGN KEY (PROFILE_ID)
     REFERENCES dbo.AUDIT_PROFILE(PROFILE_ID) ON UPDATE CASCADE,
     CONSTRAINT RefDOCUMENT359 FOREIGN KEY (SDID)
     REFERENCES dbo.DOCUMENT(SDID)
)
go


	CREATE TABLE dbo.AUDIT_EVENT_DETAIL(
     EVENT_ID                numeric(19, 0)     NOT NULL,
     EVENT_DETAIL_ID         numeric(19, 0)     NOT NULL,
     EVENT_PARAM             varchar(60)        NULL,
     EVENT_VALUE_1_PARAM     varchar(60)        NULL,
     EVENT_VALUE_1           varchar(4000)      NULL,
     EVENT_VALUE_2_PARAM     varchar(60)        NULL,
     EVENT_VALUE_2           varchar(4000)      NULL,
     EVENT_NOTE              varchar(4000)      NULL,
     CONSTRAINT PK_AUDIT_EVENT_DETAIL PRIMARY KEY CLUSTERED (EVENT_ID, EVENT_DETAIL_ID),
     CONSTRAINT RefAUDIT_EVENT375 FOREIGN KEY (EVENT_ID)
     REFERENCES dbo.AUDIT_EVENT(EVENT_ID)
)
go


	CREATE TABLE dbo.AUDIT_EVENT_TYPE(
     EVENT_TYPE_ID              numeric(19, 0)     NOT NULL,
     EVENT_TYPE_DESCRIPTION     varchar(60)        NULL,
     REPORT_GROUP               numeric(19, 0)     NULL,
     ENABLED                    bit                CONSTRAINT [DF__AUDIT_EVENT_TYPE__ENABLED] DEFAULT 1 NOT NULL,
     ACTIONTYPE                 varchar(12)        NULL,
     EXPORT                     bit                CONSTRAINT [DF__AUDIT_EVENT_TYPE__EXPORTED] DEFAULT 0 NOT NULL,
     ExportChanged              datetime           NULL,
     VISIBLE                    bit                CONSTRAINT [DF__AUDIT_EVENT_TYPE__VISIBLE] DEFAULT 1 NOT NULL,
     CONSTRAINT PK_AUDIT_EVENT_TYPE PRIMARY KEY CLUSTERED (EVENT_TYPE_ID)
     WITH FILLFACTOR = 80
)
go


	CREATE TABLE dbo.AUDIT_PROFILE(
     PROFILE_ID           numeric(19, 0)     NOT NULL,
     LOGINNAME            varchar(32)        NULL,
     PVID                 numeric(19, 0)     NULL,
     OS_USER_NAME         varchar(30)        NULL,
     OS_MACHINE_NAME      varchar(64)        NULL,
     OS_TERMINAL_NAME     varchar(16)        NULL,
     OS_PROGRAM_NAME      varchar(64)        NULL,
     WORKSTATION_NAME     varchar(128)       NULL,
     LOCID                numeric(19, 0)     NULL,
     CONSTRAINT PK_AUDIT_PROFILE PRIMARY KEY CLUSTERED (PROFILE_ID),
     CONSTRAINT RefUSR376 FOREIGN KEY (PVID)
     REFERENCES dbo.USR(PVID),
     CONSTRAINT RefLOCREG377 FOREIGN KEY (LOCID)
     REFERENCES dbo.LOCREG(LOCID)
)
go


Here is what I got so far


SELECT 
 ae.EVENT_TIMESTAMP as Login_time,
 ae1.EVENT_TIMESTAMP as Logout_time,
 datediff(hh,ae.EVENT_TIMESTAMP,ae1.EVENT_TIMESTAMP) AS timediff,
 aet.EVENT_TYPE_DESCRIPTION, 
 aed.EVENT_VALUE_1_PARAM, 
 aed.EVENT_VALUE_1, 
 aed.EVENT_PARAM, 
 aed.EVENT_VALUE_2_PARAM, 
 aed.EVENT_VALUE_2, 
 aed.EVENT_DETAIL_ID, 
 l.ABBREVNAME, 
 pp.searchname, 
 pp.patientid, 
 u.FIRSTNAME, 
 u.MIDDLENAME, 
 u.LASTNAME, 
 u.LOGINNAME
 INTO #temp 
 FROM AUDIT_PROFILE ap 
 INNER JOIN AUDIT_EVENT ae ON ap.PROFILE_ID = ae.PROFILE_ID
 INNER JOIN AUDIT_EVENT ae1 ON ap.PROFILE_ID = ae1.PROFILE_ID 
 LEFT OUTER JOIN USR u ON ap.LOGINNAME = u.LOGINNAME 
 INNER JOIN LOCREG l ON u.HOMELOCATION = l.LOCID
 LEFT OUTER JOIN AUDIT_EVENT_DETAIL aed ON ae.EVENT_ID = aed.EVENT_ID and ae1.EVENT_ID = aed.EVENT_ID 
 INNER JOIN AUDIT_EVENT_TYPE aet ON ae.EVENT_TYPE_ID = aet.EVENT_TYPE_ID 
 INNER JOIN AUDIT_EVENT_TYPE aet1 on ae1.EVENT_TYPE_ID = aet1.EVENT_TYPE_ID 
 LEFT OUTER JOIN vPatientProfile pp ON ae.PID = pp.pid and ae1.PID = pp.pid
 WHERE ae.EVENT_TIMESTAMP between '2013-11-19 00:00:00' and '2013-11-19 23:59:59' 
	AND ae1.EVENT_TIMESTAMP between '2013-11-19 00:00:00' and '2013-11-19 23:59:59'
	AND u.PVID in (1667380756709620) 
	AND ae.EVENT_TYPE_ID in (100)
	AND ae1.EVENT_TYPE_ID in (103)

 
 select a.login_time,
		a.logout_time,		
        Hours         = datepart(hour,DateDif),
        Minutes       = datepart(minute,DateDif),
        Seconds       = datepart(second,DateDif)
from 
        (
        select
               
                DateDif = Logout_time-Login_time, 
                aa.*
        from
                (  -- Test Data
                Select  
                        Login_time = convert(datetime,t.Login_time),
                        Logout_time = convert(datetime,t.Logout_time)
                        from #temp t 
                ) aa
        ) a
        
drop table #temp


results


login_time	logout_time	Hours	Minutes	Seconds
2013-11-19 11:57:08.410	2013-11-19 13:20:52.327	1	23	43
2013-11-19 11:57:08.410	2013-11-19 13:20:54.757	1	23	46
2013-11-19 11:57:08.410	2013-11-19 13:20:55.193	1	23	46
2013-11-19 11:57:08.410	2013-11-19 15:16:01.800	3	18	53
2013-11-19 11:57:08.410	2013-11-19 15:16:03.710	3	18	55
2013-11-19 11:57:08.410	2013-11-19 15:16:04.193	3	18	55
2013-11-19 13:27:07.730	2013-11-19 13:20:52.327	23	53	44
2013-11-19 13:27:07.730	2013-11-19 13:20:54.757	23	53	47
2013-11-19 13:27:07.730	2013-11-19 13:20:55.193	23	53	47
2013-11-19 13:27:07.730	2013-11-19 15:16:01.800	1	48	54
2013-11-19 13:27:07.730	2013-11-19 15:16:03.710	1	48	55
2013-11-19 13:27:07.730	2013-11-19 15:16:04.193	1	48	56



its not showing accurate enough results to calculate totals.

any suggestions?

Edited by - murd on 11/22/2013 11:18:15

murd
Starting Member

23 Posts

Posted - 11/25/2013 :  13:02:02  Show Profile  Reply with Quote
anyone think this is possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/26/2013 :  05:34:40  Show Profile  Reply with Quote
whats your expected results? make sure you post proper sample data and explain what you want

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
268 Posts

Posted - 11/26/2013 :  14:10:04  Show Profile  Reply with Quote
quote:
Originally posted by murd

login_time	logout_time	Hours	Minutes	Seconds
2013-11-19 13:27:07.730	2013-11-19 13:20:52.327	23	53	44
2013-11-19 13:27:07.730	2013-11-19 13:20:54.757	23	53	47
2013-11-19 13:27:07.730	2013-11-19 13:20:55.193	23	53	47




Those rows don't seem logical/valid. How can the login time be after the logout time?
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 11/26/2013 :  16:05:11  Show Profile  Reply with Quote
quote:
Originally posted by ScottPletcher

quote:
Originally posted by murd

login_time	logout_time	Hours	Minutes	Seconds
2013-11-19 13:27:07.730	2013-11-19 13:20:52.327	23	53	44
2013-11-19 13:27:07.730	2013-11-19 13:20:54.757	23	53	47
2013-11-19 13:27:07.730	2013-11-19 13:20:55.193	23	53	47




Those rows don't seem logical/valid. How can the login time be after the logout time?



Exactlty what I said, so lost
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 11/26/2013 :  16:09:34  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

whats your expected results? make sure you post proper sample data and explain what you want

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




login_time logout_time Hours Minutes Seconds
2013-11-19 11:57:08.410 2013-11-19 13:20:52.327 1 23 43
2013-11-19 11:57:08.410 2013-11-19 13:20:54.757 1 23 46
2013-11-19 11:57:08.410 2013-11-19 13:20:55.193 1 23 46
2013-11-19 11:57:08.410 2013-11-19 15:16:01.800 3 18 53
2013-11-19 11:57:08.410 2013-11-19 15:16:03.710 3 18 55
2013-11-19 11:57:08.410 2013-11-19 15:16:04.193 3 18 55
2013-11-19 13:27:07.730 2013-11-19 13:20:52.327 23 53 44
2013-11-19 13:27:07.730 2013-11-19 13:20:54.757 23 53 47
2013-11-19 13:27:07.730 2013-11-19 13:20:55.193 23 53 47
2013-11-19 13:27:07.730 2013-11-19 15:16:01.800 1 48 54
2013-11-19 13:27:07.730 2013-11-19 15:16:03.710 1 48 55
2013-11-19 13:27:07.730 2013-11-19 15:16:04.193 1 48 56

I want to take this set of data when accurate for example and calculate total time for logins basically

Edited by - murd on 11/26/2013 16:20:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/27/2013 :  04:41:33  Show Profile  Reply with Quote
ok and what about your required output for the data above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 11/27/2013 :  09:47:00  Show Profile  Reply with Quote
That output is what I get when I ran the query. I would like it to look something like

login_time logout_time Hours Minutes Seconds
____________________________________________________________________
2013-11-19 11:57:08.410 | 2013-11-19 13:20:52.327 | 1 | 23 | 43
2013-11-19 13:27:07.730 | 2013-11-19 15:16:01.800 | 1 | 48 | 54
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/27/2013 :  10:41:03  Show Profile  Reply with Quote
what happened to in between rows?
ie

2013-11-19 11:57:08.410	2013-11-19 15:16:01.800	3	18	53
2013-11-19 11:57:08.410	2013-11-19 15:16:03.710	3	18	55
2013-11-19 11:57:08.410	2013-11-19 15:16:04.193	3	18	55
2013-11-19 13:27:07.730	2013-11-19 13:20:52.327	23	53	44
2013-11-19 13:27:07.730	2013-11-19 13:20:54.757	23	53	47
....


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 11/27/2013 :  11:31:29  Show Profile  Reply with Quote
^ right good question, Those rows are not accurate at all which is the frustrating part. I tested myself with the logins. I logged in at 2013-11-19 11:57:08.410 then logout at 2013-11-19 13:20:52.327 for example. For some reason, the DB would create multiple logout timestamps which makes it even harder to make the correct calculations.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/27/2013 :  11:44:51  Show Profile  Reply with Quote
you need to first fix the source data to avoid these unwanted rows before you calculate the time diff.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

murd
Starting Member

23 Posts

Posted - 11/27/2013 :  13:57:09  Show Profile  Reply with Quote
I'll look more into that thanks!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000