Need urgent query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Need urgent query

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 02:39:09

Hello All

I have a table like this.

AgentID StartEvent EndEvent StartTime EndTime
1 login idle 11:05 11:10
1 idle Ans 11:10 11:15
1 Ans Logout 11:15 11:16
2 login idle 10:05 10:07
2 idle logout 10:07 10:08

where StartTime and EndTime are datetime fields in DB.
I need the difference between login and logout for each agent. How to do this?

I tried
SELECT DATEDIFF(MI,(SELECT STARTTIME FROM AGENTTRANSITION WHERE STARTEVENT='login'),(SELECT ENDTIME FROM AGENTTRANSITION WHERE ENDEVENT='logout')) AS LOGGEDINTIME

but this returns error as :: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Plz help me urgently.

I need o/p as

AgentId LoggedInTime(Min)
1 11
2 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 02:47:10

[Hint]
1. Use Group By AgentID
2. Use DateDiff() to calc the time difference
3. Find start time using min()
4. Find end time using max()
[/Hint]

-----------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 03:21:33

Also refer
http://vyaskn.tripod.com/searching_date_time_values.htm

Madhivanan

Failing to plan is Planning to fail

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 04:24:51

Actually my table contains multiple records of login-logout info of one agent. So max and min functions won't help in this case.

Plz suggest other way. Thanks

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 04:34:37

Then post same sample data and expected result. Also define your criteria

----------------------------------
'KH'
I do work from home but I don't do homework

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 05:06:15

AgentID StartEvent EndEvent StartTime EndTime
1 login idle 11:05 11:10
1 idle Ans 11:10 11:15
1 Ans Logout 11:15 11:16
2 login idle 10:05 10:07
2 idle logout 10:07 10:08
1 login idle 9:20 9:22
1 idle logout 9:22 9:23

AgentId TotalLoggedInTime(Min)
1 14
2 3

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 05:19:12

>> where StartTime and EndTime are datetime fields in DB.
Does your StartTime & EndTime contain date ?

----------------------------------
'KH'
I do work from home but I don't do homework

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 05:24:24

yes

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 05:32:17

Without date, i don't have a solution at all.

select	AgentID, sum(datediff(minute, Start_Time, End_Time)) as tm
from
(
	select	i.AgentID, i.StartTime as Start_Time, 
		(select top 1 x.EndTime from #temp x where x.AgentID = i.AgentID and x.EndEvent = 'logout' and x.EndTime > i.StartTime order by x.EndTime) as End_Time
	from 	#temp i
	where	StartEvent	= 'login'
	group by i.AgentID, i.StartTime
) as t
group by AgentID

----------------------------------
'KH'
I do work from home but I don't do homework

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 05:35:20

Try

select Agentid,Sum(DateDiff(minute,login,logout)) Duration from yourTable
Group by Agentid

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 05:42:39

quote:

1 login idle 11:05 11:10 <== rec 1
1 idle Ans 11:10 11:15 <== rec 2
1 Ans Logout 11:15 11:16 <== rec 3

Is the endtime of rec 1 = starttime of rec 2 ?

----------------------------------
'KH'
I do work from home but I don't do homework

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 06:05:22

Yes, as these events are happening in sequence

Njoy Life

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 06:08:39

100rry Madhivanan, but its not working. For DateDiff(), we need datetime fields.

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 06:46:52

Are Start_Time and End_Time of varchar datatype?

Madhivanan

Failing to plan is Planning to fail

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 07:02:56

No, they are of SQL Datetime datatype.

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 07:09:03

Then, you should get correct answer


select Agentid,Sum(DateDiff(minute,login,logout)) Duration from
(
	select 1 as agentid , '2006-12-11 11:05' login, '2006-12-11 11:10' logout union all
	select 1, '2006-12-11 11:10', '2006-12-11 11:15' union all
	select 1, '2006-12-11 11:15','2006-12-11 11:16'union all
	select 2, '2006-12-11 10:05', '2006-12-11 10:07'union all
	select 2, '2006-12-11 10:07', '2006-12-11 10:08'union all
	select 1, '2006-12-11 9:20', '2006-12-11 9:22'union all
	select 1, '2006-12-11 9:22', '2006-12-11 9:23'
) T 
group by Agentid

Madhivanan

Failing to plan is Planning to fail

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 07:28:33

I'm not getting......why to put values from DB in query........ Also in first line login and logout are the DB values....how the DateDiff() take them?
Plz clarify ur query.......

Njoy Life

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 07:38:30

quote:
Originally posted by swatib

Yes, as these events are happening in sequence

Njoy Life

Then the query can be very much simplified to Mahadivan's solution.

select AgentID, Sum(DateDiff(minute, StartTime, EndTime)) Duration
from   YourTable
group by AgentID

----------------------------------
'KH'
I do work from home but I don't do homework

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 07:46:51

Explaination of Madhivanan's code

	select 1 as agentid , '2006-12-11 11:05' login, '2006-12-11 11:10' logout union all
	select 1, '2006-12-11 11:10', '2006-12-11 11:15' union all
	select 1, '2006-12-11 11:15','2006-12-11 11:16'union all
	select 2, '2006-12-11 10:05', '2006-12-11 10:07'union all
	select 2, '2006-12-11 10:07', '2006-12-11 10:08'union all
	select 1, '2006-12-11 9:20', '2006-12-11 9:22'union all
	select 1, '2006-12-11 9:22', '2006-12-11 9:23'

The above will generate a data set with 3 columns namely : agentid, login and logout. (Your table's column name is named differently login = StartTime, Logout = EndTime)

As you have specified that the event are occurs in sequence : login - idle - logout, so the StartEvent and EndEvent columns are irrelevant.
So to calculate the time different in minute, datediff(minute, login, logout) or in your table column naming datediff(minute, starttime, endtime).

Hope above clear things up for you.

----------------------------------
'KH'
I do work from home but I don't do homework

swatib
Posting Yak Master

173 Posts

Posted - 2006-01-23 : 23:42:50

Thank you so much to both of you...
You both have helped me a lot for this query......

Yes, I executed this query and its running properly to my expectation.....

Thanks again..........

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 00:27:39

>>Yes, I executed this query and its running properly to my expectation.....

Well. Anyway you need to understand the logic used

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources