因为电信的对单是要和其它运营商区别开来的,我采用了以下的方式:
--删除
--delete Vo_Charge insert into Vo_Charge(Callerno,Tmin)
select callerno,
CEILING(DATEDIFF(second,StartTime,EndTime)/cast(60 as float))
from vo_call_log where startTime>'2006-07-01 00:00:01' and EndTime<'2006-07-31 20:00:01'
and Callerno not like '81%' and Callerno not like'82%' and Callerno not like'83%'
or Callerno not like '84%'and Callerno not like'85%'and Callerno not like'86%'--得到总的update Vo_Charge
set Msum = 2 where Tmin<=3
update Vo_Charge
set Msum = Tmin-1
where Tmin>3
--计算出每一次拨打产生的跳次(费用)
--以下是去掉重复拨打的情况
SELECT right(callerno,11),count(*) AS Cnum,sum(Tmin) AS Minutes,sum(Msum) AS Mon
FROM Vo_Charge
WHERE Callerno not like '1%' and Callerno not like '0%'
GROUP BY Callerno
ORDER BY callerno
结果不对.我是想得到除了手机以外\除了81到86以外的所有数据.有没有办法给一个好一点存储过程呀?
--删除
--delete Vo_Charge insert into Vo_Charge(Callerno,Tmin)
select callerno,
CEILING(DATEDIFF(second,StartTime,EndTime)/cast(60 as float))
from vo_call_log where startTime>'2006-07-01 00:00:01' and EndTime<'2006-07-31 20:00:01'
and Callerno not like '81%' and Callerno not like'82%' and Callerno not like'83%'
or Callerno not like '84%'and Callerno not like'85%'and Callerno not like'86%'--得到总的update Vo_Charge
set Msum = 2 where Tmin<=3
update Vo_Charge
set Msum = Tmin-1
where Tmin>3
--计算出每一次拨打产生的跳次(费用)
--以下是去掉重复拨打的情况
SELECT right(callerno,11),count(*) AS Cnum,sum(Tmin) AS Minutes,sum(Msum) AS Mon
FROM Vo_Charge
WHERE Callerno not like '1%' and Callerno not like '0%'
GROUP BY Callerno
ORDER BY callerno
结果不对.我是想得到除了手机以外\除了81到86以外的所有数据.有没有办法给一个好一点存储过程呀?
or Callerno not like '84%'and Callerno not like'85%'and Callerno not like'86%'是意识??//
patindex('[^8]%' ,Callerno ) > 0
or
patindex('[8][^123456]%' ,Callerno ) <= 0
or Callerno not like '84%'and Callerno not like'85%'and Callerno not like'86%'是不是将“or”改为“and”(“81~86 是铁通和网通的用户.要去掉.”)
Callerno not like '81%' and Callerno not like'82%' and Callerno not like'83%'
and Callerno not like '84%'and Callerno not like'85%'and Callerno not like'86%'