搜索发送失败，短信发重记录

--搜索发送失败，短信发重记录

declare @startDate datetime

declare @endDate datetime

set @startDate = '2010-10-26'

set @endDate = '2010-10-27'

declare @PhoneNumber varchar(100)

declare @RechargeMoney varchar(100)

declare @AddTime datetime

declare Cur cursor  for

select  PhoneNumber,RechargeMoney  from dbo.view_FailureRecharge

where addtime between @startDate and @endDate

 group by PhoneNumber,RechargeMoney

open Cur

Fetch next From Cur Into @PhoneNumber,@RechargeMoney

While @@fetch_status=0    

Begin

declare @countmsg int

select @countmsg = count(1) from ( select Count(1) as countMsg,MsgContent from inbox as a where msgcontent like '%' + @PhoneNumber + '%'  

and ReceiveTime between @startDate and @endDate Group by MsgContent ) as b

if(@countmsg  > 1)

begin

select *  from inbox as a where msgcontent like '%' + @PhoneNumber + '%'  

and ReceiveTime between @startDate and @endDate

end

 

    Fetch Next From Cur Into @PhoneNumber,@RechargeMoney

End  

Close Cur  

Deallocate Cur

 

 

 

 

--统计各卡冲值金额

declare @startDate datetime

declare @endDate datetime

set @startDate = '2010-10-26'

set @endDate = '2010-10-27'

select MOdemID,

(select top 1 [Name] from smsmodem where smsmodem.ModemID = RechargeMsg.MOdemID) as cardname,

sum(RechargeMoney) as RechargeMoney

from dbo.RechargeMsg where addtime between @startDate and @endDate  group by MOdemID