自己搞定了不过还是谢谢大家
string endCall = "";
StringBuilder sb = new StringBuilder();
//创建临时表并查询出所有呼入记录
sb.Append("create global temporary table temptable on commit preserve rows as ");
sb.Append("select * from CDRMAIN where ");
sb.Append(" Direction=1 ");
sb.Append(timesql.ToString());
OracleConnection con = new OracleConnection(DBHelper.connectionStringAltigen);
con.Open();
OracleCommand comd = new OracleCommand(sb.ToString(), con);
comd.CommandTimeout = 240;
comd.ExecuteNonQuery();
//总的排队等待数(过滤重复)
comd.CommandText = "select count(sessionid) from (select distinct(sessionid) from temptable where QueueDuration>0)";
int Sum = Convert.ToInt32(comd.ExecuteScalar());
//排队接通数
comd.CommandText = "select count(sessionid) from (select distinct(sessionid) from temptable where QueueDuration>0 and sessioinid in (select sessionid from temptable where TalkDuration>0) )";
int inRecordeCount = Convert.ToInt32(comd.ExecuteScalar());
//排队挂机数(总排队数-排队接通数)
endCall = (Convert.ToInt32(Sum) - Convert.ToInt32(inRecordeCount)).ToString();
--声明游标
if(@IsMp=1 and @isexchange=1)
begin
set @sqlcursor='declare GkMpExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by createon asc'
end
else if(@IsMp=1 and @isexchange=0)
begin
set @sqlcursor='declare xxxExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by isexchange asc,createon asc'
end
else if(@IsMp=0 and @isexchange=0)
begin
set @sqlcursor='declare xxxExchange_cursor CURSOR FOR select id as MPCutScoreDetailId, reguser,mprule,CutScore from xxxHistory where ismp='+cast(@IsMp as nvarchar)+' and isexchange='+cast(@isexchange as nvarchar)+' and cutscore>0 and action=1 and reguser='''+@userid+''' order by createon asc'
end
exec (@sqlcursor)
print @sqlcursor
--打开游标
open xxxExchange_cursor
--循环数据
fetch next from xxxExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore
WHILE (@@FETCH_STATUS=0 and @TotalCutScore>0)
begin
if @CutScore<=@TotalCutScore
begin
set @TotalCutScore=@TotalCutScore-@CutScore
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,CreateTime) values(@MPCutScoreDetailId,@CutScore,@MpRule,@userid,@IsMp,@isexchange,getdate())
end
else
begin
set @CutScore=@CutScore-@TotalCutScore
insert into #SubMpScoreTable(MPCutScoreDetailId,CutScore,MpRule,reguser,IsMp,IsExchange,CreateTime) values(@MPCutScoreDetailId,@TotalCutScore,@MpRule,@userid,@IsMp,@isexchange,getdate())
set @TotalCutScore=0
end
print @TotalCutScore
fetch next from xxxExchange_cursor into @MPCutScoreDetailId,@reguser,@mprule,@CutScore
end
--关闭,释放游标资源
close xxxExchange_cursor
DEALLOCATE xxxExchange_cursor
select * from #SubMpScoreTable
drop table #SubMpScoreTable
END