Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculate TOTAL login/Logout times

Author  Topic 

murd
Starting Member

24 Posts
Posted - 2013-11-22 : 11:09:02
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?

murd
Starting Member

24 Posts
Posted - 2013-11-25 : 13:02:02
anyone think this is possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-11-26 : 05:34:40
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
Aged Yak Warrior

550 Posts
Posted - 2013-11-26 : 14:10:04
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

24 Posts
Posted - 2013-11-26 : 16:05:11
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

24 Posts
Posted - 2013-11-26 : 16:09:34
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-11-27 : 04:41:33
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

24 Posts
Posted - 2013-11-27 : 09:47:00
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

52326 Posts
Posted - 2013-11-27 : 10:41:03
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

24 Posts
Posted - 2013-11-27 : 11:31:29
^ 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

52326 Posts
Posted - 2013-11-27 : 11:44:51
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

24 Posts
Posted - 2013-11-27 : 13:57:09
I'll look more into that thanks!!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site