quote:

---

Originally posted by khtan

declare	@table table
(
	cust_id 	int,
	cust_name 	varchar(20),
	action 		varchar(20),
	date_time	datetime
)

insert into @table
select	1, 'john',  'CallOperator', 	'2006-04-10 10:00' union all
select	1, 'john',  'Attend', 		'2006-04-10 10:01' union all
select	1, 'john',  'Hold', 		'2006-04-10 10:05' union all
select	1, 'john',  'Attend', 		'2006-04-10 10:15' union all
select	2, 'jothi', 'CallOperator', 	'2006-04-10 20:00' union all
select	2, 'jothi', 'Attend', 		'2006-04-10 20:10'

select	cust_id, cust_name, sum(datediff(minute, s_datetime, e_datetime))
from
(
	select	s.cust_id, s.cust_name, 
		s.action as s_action, s.date_time as s_datetime, 
		e.action as e_action, e.date_time as e_datetime
	from	@table s inner join @table e
	on	s.cust_id	= e.cust_id
	and	e.date_time	= (select min(date_time) from @table x 
					where 	x.cust_id 	= s.cust_id
					and	x.action	= 'Attend'
					and 	x.date_time 	> s.date_time)
	where	s.action	in ('CallOperator', 'Hold')
	and	e.action	= 'Attend'
) a
group by cust_id, cust_name

---

KH

---

quote:

---

Originally posted by madhivanan

Radhiga, again I say you always want to use Cursor. If you post some sample data and the result you want, you may get better answer than you expected

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by Sarakumar
YES, I HAVE POSTED MY ACTUAL REQUIRMENT ALREADY.

---

quote:

---

Originally posted by Radhiga

Thanks a lot for ur effort..i need to verify with my PM am i allowed to use Temp table..Generally they dont encourage us...that's y im trying how can i do this without temp table...
may be i need to do it in the front end or somewhere...im also confused to proceed with this.

quote:

---

Originally posted by khtan

declare	@table table
(
	cust_id 	int,
	cust_name 	varchar(20),
	action 		varchar(20),
	date_time	datetime
)

insert into @table
select	1, 'john',  'CallOperator', 	'2006-04-10 10:00' union all
select	1, 'john',  'Attend', 		'2006-04-10 10:01' union all
select	1, 'john',  'Hold', 		'2006-04-10 10:05' union all
select	1, 'john',  'Attend', 		'2006-04-10 10:15' union all
select	2, 'jothi', 'CallOperator', 	'2006-04-10 20:00' union all
select	2, 'jothi', 'Attend', 		'2006-04-10 20:10'

select	cust_id, cust_name, sum(datediff(minute, s_datetime, e_datetime))
from
(
	select	s.cust_id, s.cust_name, 
		s.action as s_action, s.date_time as s_datetime, 
		e.action as e_action, e.date_time as e_datetime
	from	@table s inner join @table e
	on	s.cust_id	= e.cust_id
	and	e.date_time	= (select min(date_time) from @table x 
					where 	x.cust_id 	= s.cust_id
					and	x.action	= 'Attend'
					and 	x.date_time 	> s.date_time)
	where	s.action	in ('CallOperator', 'Hold')
	and	e.action	= 'Attend'
) a
group by cust_id, cust_name

---

KH

---

---

select cust_id, cust_name, total, b.billing_amt
from
(
select cust_id, cust_name, sum(datediff(minute, s_datetime, e_datetime)) as total
from
(
select s.cust_id, s.cust_name,
s.action as s_action, s.date_time as s_datetime,
e.action as e_action, e.date_time as e_datetime
from @table s inner join @table e
on s.cust_id = e.cust_id
and e.date_time = (select min(date_time) from @table x
where x.cust_id = s.cust_id
and x.action = 'Attend'
and x.date_time > s.date_time)
where s.action in ('CallOperator', 'Hold')
and e.action = 'Attend'
) a
group by cust_id, cust_name
) c 
inner join billing_info b
on c.cust_id = b.cust_id

quote:

---

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

---

quote:

---

Originally posted by khtan

"Internal SQL Server error."
Yeah I got that also. Actually i encounter this from time to time when my query gets too complicated and as usual it always near deadline when i need to rush it out when i encounter this and hence don't have the luxury to research into it. My work around is to re-write it differently.

Just did some rearch and find this. http://support.microsoft.com/kb/830466.

quote:

---

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section of this article.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

---

My SQLServer is already SP4 but I still get the error. Anyone else can help ?

---

KH

---