January 29, 2011 at 9:03 am
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...
SqlIndia
January 29, 2011 at 9:23 am
did you try using Rank() to get your sequence solution ?
, rank() over ( partition by AgentID order by AgentID, RegistrationDatetime asc ) Seq
Depending on the volume of data, this may be a well enough solution
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2011 at 11:59 am
No I haven't... any other way that do not use any function rather may be while loop with flag to the records or any other method.
Thanks..
SqlIndia
January 29, 2011 at 12:43 pm
Errr, why would you prefer a slow and inefficient while loop over a ranking function?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2011 at 3:10 pm
so you'd prefer a loop over this:
;with cteAgentSessions
as ( select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
)
select Logins.AgentID
, Logins.DT as LoginDt
, Logouts.DT as LogoutDt
, DATEDIFF(ss,Logins.DT, Logouts.DT) as SessionSS
from cteAgentSessions Logins
left join cteAgentSessions Logouts
on Logouts.AgentID = Logins.AgentID
and Logouts.Seq = Logins.Seq + 1
and Logouts.AgentStatus = 1
where Logins.AgentStatus = 0
order by AgentID, LoginDt
;
Your choice.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2011 at 4:07 am
You have valid questions...I like know how step by step workout for the results for this problem rather just use functions that do all the work for me.
I appreciate if we replace simple query with bold line...
;with cteAgentSessions
as ( select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
)
select Logins.AgentID
, Logins.DT as LoginDt
, Logouts.DT as LogoutDt
, DATEDIFF(ss,Logins.DT, Logouts.DT) as SessionSS
from cteAgentSessions Logins
left join cteAgentSessions Logouts
on Logouts.AgentID = Logins.AgentID
and Logouts.Seq = Logins.Seq + 1
and Logouts.AgentStatus = 1
where Logins.AgentStatus = 0
order by AgentID, LoginDt
;
Thanks a lot for help...
SqlIndia
January 31, 2011 at 4:56 am
Your hightlighted part is the actual ranking function.
It will generate a sequenced number (starting from 1) based on AgentID and DT and it will restart numbering from 1 if AgentID changes.
Check the resultlist by executing this
--;with cteAgentSessions
--as (
select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
--)
order by AgentID, Seq ;
or this if you want to use the cte.
;with cteAgentSessions
as (
select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
)
select AgentID,DT,AgentStatus,TeamID, Seq
from cteAgentSessions
order by AgentID, Seq ;
If you don't want to use the cteAgentSessions, you could replace it with the way we used to before SQL2005:
( select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
)[/b] Logins
left join ( select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc ) Seq
from @TBL
) Logouts
on Logouts.AgentID = Logins.AgentID
and Logouts.Seq = Logins.Seq + 1
and Logouts.AgentStatus = 1
where Logins.AgentStatus = 0
order by AgentID, LoginDt
;
Keep in min when avoiding the cte version, chances are sqlserver doesn't know it will actually be the same set of data generated by the nested table expressions and will generate two sets for it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2011 at 11:46 pm
This is brilliant... the way you have explained it...
Shall we replace rank() over function with T-SQL (or any other way)?
I mean this line select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc
This will be great help..
SqlIndia
February 1, 2011 at 12:53 am
sqlindia (1/31/2011)
This is brilliant... the way you have explained it...Shall we replace rank() over function with T-SQL (or any other way)?
I mean this line select AgentID,DT,AgentStatus,TeamID
, rank() over ( partition by AgentID order by AgentID, DT asc
This will be great help..
This rank() function is new with sql2005.
I think it's going to stay for a while 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply