April 20, 2006 at 6:59 pm
hi
iam having some problem in executing the below query
pls help
declare @BillingTbl table
(
cust_id int,
cust_name varchar(20),
Amt money
)
insert into @BillingTbl
select 1, 'john', 1000 union all
select 2, 'jothi', 2000 union all
select 3, 'Shyam', 3000
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 b.cust_id, b.cust_name, b.Amt from
(
select cust_id, cust_name, sum(datediff(minute, s_datetime, e_datetime))as dt
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 @BillingTbl b
on c.cust_id = b.cust_id
this is my table.
now i want to display the report as the follows
id name Waitingtime date Amt
1 john 11(Sec) 4/10/2006 1000
2 jothi 10 4/10/2006 2000
3 null null null 3000
But when I try to run the above I get the error as
Server: Msg 8624, Level 16, State 13, Line 31
Internal SQL Server error.
thanks in advance
April 20, 2006 at 10:09 pm
To get rid of the internal error you cannot use the correlated subquery as a join operator., Just move it to the where clause,
And to get the results you want, you have to "Right" join to @BillingTbl
select b.cust_id, b.cust_name, c.dt, b.Amt
from (
select cust_id, cust_name, sum(datediff(minute, s_datetime, e_datetime))as dt
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
Where 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)
and s.action in ('CallOperator', 'Hold')
and e.action = 'Attend'
) a
group by cust_id, cust_name
) c
Right join @BillingTbl b on c.cust_id = b.cust_id
Results:
cust_id,cust_name,dt,Amt
1,john,11,1000.0000
2,jothi,10,2000.0000
3,Shyam,Null,3000.0000
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply