SQL Server Performance

T-SQL - Calculate Login Duration

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Jan 29, 2011.

  1. sqlderby Member

    Hi All
    I need to calculate lgin hours of agents. The login and logout state code insert into table with date and time and agent info. I have tried to prepared following solution but not working properly ad that'w what I need help here....
    DECLARE @TBL table(AgentID int,DT datetime,AgentStatus int, TeamID varchar(8))

    -- AgentStatus = 0 // Login
    -- AgentStatus = 1 // Logout

    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:15:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:25:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 12:00:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 13:30:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 16:30:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 18:00:00',0,'GOGL')

    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 12:00:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 13:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 16:30:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 18:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 21:00:00',1,'GOGL')

    -- select * from @TBL

    declare @LoginTime table (seq int IDENTITY(1,1) NOT NULL, agentid int,loginDT datetime,TeamID varchar(8))
    declare @LogoutTime table (seq int IDENTITY(1,1) NOT NULL, agentid int,logoutDT datetime,TeamID varchar(8))

    INSERT INTO @LoginTime
    select AgentID,DT,TeamID FROM @TBL WHERE (AgentStatus = 0)

    INSERT INTO @LogoutTime
    select AgentID,DT,TeamID FROM @TBL WHERE (AgentStatus = 1)

    SELECT a.AgentID, a.loginDT, a.TeamID, DATEDIFF(ss,a.loginDT,b.logoutDT) as 'Duration'
    from @LoginTime a, @LogoutTime b
    where a.seq=b.seq and a.TeamID=b.TeamID
    This will give issue on seq number of login(0) and logout(1) records as result of miscalculation of login duration.
    Pls help...
  2. FrankKalis Moderator

    Given the above sample data, what would you like to see as output?
  3. sqlderby Member

    Should be Like this...
    "AgentID","LoginDT","LogoutDT","LoginTimeSecond"
    90020,01/20/11 10:00 AM,01/20/11 10:15 AM,900
    90020,01/20/11 10:25 AM,01/20/11 12:00 PM,5700
    90020,01/20/11 01:30 PM,01/20/11 04:30 PM,10800
    90021,01/20/11 11:00 AM,01/20/11 11:30 AM,1800
    90021,01/20/11 01:00 PM,01/20/11 01:30 PM,1800
    OR
    90020,01/20/11 10:00 AM,01/20/11 04:30 PM,17400
    90021,01/20/11 11:00 AM,01/20/11 01:30 PM,3600
    Thanks..
  4. sqlderby Member

    any help...
  5. RamJaddu Member

    If I understand correctly this would help you
    ---Code below
    DECLARE @TBL table(AgentID int,DT datetime,AgentStatus int, TeamID varchar(8))

    -- AgentStatus = 0 // Login
    -- AgentStatus = 1 // Logout

    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:15:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:25:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 12:00:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 13:30:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 16:30:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 18:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 12:00:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 13:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 16:30:00',1,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 18:00:00',0,'GOGL')
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90021,'1/20/2011 21:00:00',1,'GOGL')

    declare @LoginTime table ( agentid int,loginDT datetime,TeamID varchar(8))
    declare @LogoutTime table ( agentid int,logoutDT datetime,TeamID varchar(8))
    INSERT INTO @LoginTime
    select
    AgentID
    ,Min(DT)
    ,TeamID

    FROM @TBL WHERE (AgentStatus = 0)
    group by AgentID,TeamID
    INSERT INTO @LogoutTime
    select AgentID,Max(DT),TeamID FROM @TBL WHERE (AgentStatus = 1)
    group by AgentID,TeamID
    select l.Agentid,LoginDT,LogoutDt,datediff(ss,LoginDT,LogoutDt) from @LoginTime L
    Inner join @LogoutTime M
    on L.Agentid = M.Agentid
  6. sqlderby Member

    Here it is another version...
    declare @TeamID int,@DT datetime

    set @TeamID = 101
    set @DT = '1/20/2011'

    declare @TBL table(AgentID int,DT datetime,AgentStatus int,TeamID int)

    -- AgentStatus = 0 // Login
    -- AgentStatus = 1 // Logout

    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 9:00:00',1,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 10:00:00',0,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 11:00:00',1,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 12:00:00',0,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90020,'1/20/2011 13:00:00',1,101)

    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90022,'1/20/2011 10:00:00',1,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90022,'1/20/2011 11:00:00',0,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90022,'1/20/2011 11:30:00',1,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90022,'1/20/2011 13:00:00',0,101)
    insert into @TBL(AgentID,DT,AgentStatus,TeamID) values(90022,'1/20/2011 13:30:00',1,101)

    --select * from @TBL

    declare @TBL_Login table(ID int identity(1,1),AgentID int,DT datetime)
    declare @TBL_Logout table(ID int identity(1,1),AgentID int,DT datetime)

    insert into @TBL_Login(AgentID,DT)
    select AgentID,DT from @TBL where (TeamID = @TeamID) and (AgentStatus = 0)
    and (DT >= DATEADD(DD,0,DATEDIFF(DD,0,@DT))) and (DT < DATEADD(DD,1,DATEADD(DD,0,DATEDIFF(DD,0,@DT))))
    order by AgentID,DT

    insert into @TBL_Logout(AgentID,DT)
    select T1.AgentID,T1.DT from @TBL T1
    inner join
    (
    select AgentID,MIN(DT) DT from @TBL_Login group by AgentID
    ) T2 on T1.AgentID = T2.AgentID and T1.DT > T2.DT
    where (T1.TeamID = @TeamID) and (T1.AgentStatus = 1)
    and (T1.DT >= DATEADD(DD,0,DATEDIFF(DD,0,@DT))) and (T1.DT < DATEADD(DD,1,DATEADD(DD,0,DATEDIFF(DD,0,@DT))))
    order by T1.AgentID,T1.DT


    select T1.AgentID ,T1.DT LoginDT,T2.DT LogoutDT ,LoginTimeSecond = DATEDIFF(SS,T1.DT,T2.DT)
    from @TBL_Login T1 inner join @TBL_Logout T2 on T1.ID = T2.ID and T1.AgentID = T2.AgentID

    select AgentID,MIN(LoginDT) LoginTime,MAX(LogoutDT) LogoutTime,SUM(LoginTimeSecond) TotalLoginTimeSecond
    from
    (
    select T1.AgentID ,T1.DT LoginDT,T2.DT LogoutDT ,LoginTimeSecond = DATEDIFF(SS,T1.DT,T2.DT)
    from @TBL_Login T1 inner join @TBL_Logout T2 on T1.ID = T2.ID and T1.AgentID = T2.AgentID
    )
    T
    group by AgentID
    Regards

Share This Page