工作Sql使用

USE [ESMS]
GO
/****** Object:  StoredProcedure [dbo].[SPHistoryReacrdDay]    Script Date: 05/12/2014 11:27:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SPHistoryReacrdDay] @ToDay datetime
as
--开始事务
BEGIN TRAN
--不显示计数信息
SET NOCOUNT ON
--定义参数
declare @Username varchar(20),@SubmitCount int,
@UserSuccessCount int, @UserErrorCount int = 0,
@CUSNAME varchar(32),@successCount int,
@errorCount int,@NotBackCount int,
@SuccessCM int,@SuccessCU int, @SuccessCT int,
@year int,@month varchar(2),@day varchar(2),
@Sp_Id  varchar(20),@SendNum int,
@Sp_Tra int,@Sp_Traffic varchar(50),
@SendCMTRAFFIC varchar(50),
@SendCUTRAFFIC varchar(50),
@SendCTTRAFFIC varchar(50),
@SSendCMTRAFFIC varchar(50),
@SSendCUTRAFFIC varchar(50),
@SSendCTTRAFFIC varchar(50)
--定义游标查询当天的数据
declare Record_cursor cursor for (select UserName, SubmitCount from (
select UserName,SUM(SubmitCount) SubmitCount from SMS_SubmitRecord where 
CONVERT(varchar(8),SubmitDate,112)=CONVERT(varchar(8),@ToDay,112)
group by UserName) b)
--定义游标查询通道id和发送量
declare SpSendAmount_cursor cursor for (
 --添加通道统计表
     select SP_ID,SendNum,Sp_Tra,
		CONVERT(varchar(50),cast(round(CONVERT(float,Sp_Tra)/POWER(CAST(1024 as bigint),p.Pr),2) as float))
	   	+p.Suffix Sp_Traffic
	   from 
	  (
	  select SP_ID,COUNT(*) SendNum,sum(datalength(SMS_CONTENT)) Sp_Tra
	  from SMS_MT 
	  where REPORT_STAT='DELIVRD' and
	  CONVERT(varchar(8),INTO_TIME,112)=CONVERT(varchar(8),@ToDay,112)
	  group by SP_ID
	  ) b cross join SMS_Suffix p 
	  where 
	  (convert(float,Sp_Tra)/POWER(cast(1024 as bigint),p.pr)<1024 
	  and convert(float,Sp_Tra)/POWER(cast(1024 as bigint),p.pr)>=1 or 
	 (Sp_Tra=0 and p.pr=0) or 
	 (Sp_Tra>=1125899906842624 and p.pr=4)))
--打开游标
open Record_cursor 
--拿到游标赋值
FETCH NEXT FROM Record_cursor INTO @UserName,@SubmitCount
--循环数据
while @@FETCH_STATUS=0   --循环读取
     begin
	 select @CUSNAME=a2.CUSNAME from SSO_USER_INFO a1,SSO_CUSTOMERS a2
	 where a1.CUSID=a2.CUSID and a1.LOGONID=@UserName; 
	 select @UserErrorCount=sum(NumberCount) from dbo.SMS_RejectMessage where UserName=@UserName
	 and CONVERT(varchar(8),SubmitDate,112)=CONVERT(varchar(8),@ToDay,112) group by UserName;
  	select @successCount=SuccessCount,@errorCount=ErrorCount,@NotBackCount=NotBackCount,
	@SuccessCM=SuccessCM,@SuccessCU=SuccessCU,@SuccessCT=SuccessCT,@SendCMTRAFFIC=SendMTTRAFFIC,
	@SendCUTRAFFIC=SendCUTRAFFIC,@SendCTTRAFFIC=SendCTTRAFFIC,@SSendCMTRAFFIC=SSendMTTRAFFIC,
	@SSendCUTRAFFIC=SSendCUTRAFFIC,@SSendCTTRAFFIC=SSendCTTRAFFIC
	from 
	(select  USERID,SuccessCount,ErrorCount,NotBackCount,SuccessCM,SuccessCU,SuccessCT,SendMTTRAFFIC,SendCUTRAFFIC,
	SendCTTRAFFIC,
	convert(varchar(50),
	cast(round(convert(float,SendMTTRAFFIC)/POWER(cast(1024 as bigint),p.Pr),2) as float))
	+p.Suffix SSendMTTRAFFIC,
	convert(varchar(50),
	cast(round(convert(float,SendCUTRAFFIC)/POWER(cast(1024 as bigint),p2.Pr),2) as float))
	+p2.Suffix SSendCUTRAFFIC,
	convert(varchar(50),
	cast(round(convert(float,SendCTTRAFFIC)/POWER(cast(1024 as bigint),p3.Pr),2) as float))
	+p3.Suffix SSendCTTRAFFIC
	from (select USERID,sum(successCount) SuccessCount,sum(errorCount) ErrorCount,sum(NotBackCount) NotBackCount,
	sum(SuccessCM) SuccessCM,sum(SuccessCU) SuccessCU,sum(SuccessCT) SuccessCT,
	sum(SuccessCM * CMChat) SendMTTRAFFIC,
	sum(SuccessCU * CUChat) SendCUTRAFFIC,
	sum(SuccessCT * CTChat) SendCTTRAFFIC
	from 
	(  select USERID,successCount,errorCount,NotBackCount,CountChat,SuccessCM,SuccessCU,SuccessCT,
      CountChat+(select description from SYS_CODELKUP where listname='ConfigMessageLevel' and code='CM') CMChat,
      CountChat+(select description from SYS_CODELKUP where listname='ConfigMessageLevel' and code='CU') CUChat,
      CountChat+(select description from SYS_CODELKUP where listname='ConfigMessageLevel' and code='CT') CTChat
      from 
      (
     select USERID,
	 SUM(case when REPORT_STAT='DELIVRD' then 1 else 0 end) successCount,
	 SUM(case when REPORT_STAT<>'DELIVRD' then 1 else 0 end) errorCount,
	 SUM(case when REPORT_STAT IS NULL   then 1 else 0 end) NotBackCount,
	 DATALENGTH(SMS_CONTENT) CountChat,
	 SUM(case when MOBILE_NO_TYPE=1 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCM,
	 SUM(case when MOBILE_NO_TYPE=2 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCU,
	 SUM(case when MOBILE_NO_TYPE=3 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCT
	 from SMS_MT 
	 where 
	 Convert(varchar(8),INTO_TIME,112)=Convert(varchar(8),@ToDay,112)
	 group by USERID,SMS_CONTENT having USERID=@Username) g) b 
	 group by USERID) c cross join SMS_Suffix p cross join SMS_Suffix p2 Cross join SMS_Suffix p3
	 where (convert(float,SendMTTRAFFIC)/POWER(cast(1024 as bigint),p.pr)<1024 
	 and convert(float,SendMTTRAFFIC)/POWER(cast(1024 as bigint),p.pr)>=1 or 
	 (SendMTTRAFFIC=0 and p.pr=0) or 
	 (SendMTTRAFFIC>=1125899906842624 and p.pr=4))
	 and (convert(float,SendCUTRAFFIC)/POWER(cast(1024 as bigint),p2.pr) < 1024 
	 and convert(float,SendCUTRAFFIC)/POWER(cast(1024 as bigint),p2.pr)>=1 
	 or (SendCUTRAFFIC=0 and p2.pr=0) or 
	 (SendCUTRAFFIC>=1125899906842624 and p2.pr=4))
	 and (convert(float,SendCTTRAFFIC)/POWER(cast(1024 as bigint),p3.pr) < 1024 
	 and convert(float,SendCTTRAFFIC)/POWER(cast(1024 as bigint),p3.pr)>=1 
	 or (SendCTTRAFFIC=0 and p3.pr=0) or 
	 (SendCTTRAFFIC>=1125899906842624 and p3.pr=4)))d;
	 set @year=year(@ToDay)
	 set @month = right(replicate('0', month(@ToDay)) + convert(varchar(4),month(@ToDay)), 2)
     set @day = right(replicate('0', day(@ToDay)) + convert(varchar(4),day(@ToDay)), 2)
     set @UserSuccessCount=@SubmitCount-@UserErrorCount;
    
	 insert into SMS_HistoryRecord(HistoryYear, HistoryMonth, HistoryDay,
	 CustomerName, UserName, UserSubmitCount, UserSubmitSuccess, 
	 UserSubmitError, SendStateSuceessCount, SendStateErrorCount, 
	 SendStateNoBackCount, CMNumberCount, CUNumberCount, CTNumberCount,CurrentDate,SendCMTRAFFIC,
	 SendCUTRAFFIC,SendCTTRAFFIC,SSendCMTRAFFIC,SSendCUTRAFFIC,SSendCTTRAFFIC)values
	 (@year,@month,@day,@CUSNAME,@Username,@SubmitCount,@UserSuccessCount,
	 @UserErrorCount,@successCount,@errorCount,@NotBackCount,@SuccessCM,@SuccessCU,@SuccessCT,@ToDay,
	 @SendCMTRAFFIC,@SendCUTRAFFIC,@SendCTTRAFFIC,@SSendCMTRAFFIC,@SSendCUTRAFFIC,@SSendCTTRAFFIC);
	 FETCH NEXT FROM Record_cursor into @UserName,@SubmitCount
   end
CLOSE Record_cursor; --关闭游标
deallocate Record_cursor; --释放游标
--打开游标
open SpSendAmount_cursor
--拿到游标赋值 
FETCH NEXT FROM SpSendAmount_cursor INTO @SP_ID,@SendNum,@Sp_Tra,@Sp_Traffic
while @@FETCH_STATUS =0 --执行循环
    begin
    insert into SMS_SP_Collect(Sp_Id,StatisticsTime,SendNum,Sp_Traffic,Sp_STraffic)values(@SP_ID,@ToDay,@SendNum,@Sp_Tra,@Sp_Traffic)
    FETCH NEXT FROM SpSendAmount_cursor INTO @SP_ID,@SendNum,@Sp_Tra,@Sp_Traffic
    end
--提交所有变更
CLOSE SpSendAmount_cursor; --关闭游标
deallocate SpSendAmount_cursor; --释放游标
COMMIT TRAN
SET NOCOUNT OFF --恢复设置

USE [ESMS]
GO
/****** Object:  StoredProcedure [dbo].[SPHistoryReacrdTest]    Script Date: 05/12/2014 11:28:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SPHistoryReacrdTest]
as
--开始事务
BEGIN TRAN
--不显示计数信息
SET NOCOUNT ON
--定义参数
declare @ToDay datetime,
@yesterday datetime,@beforeyesterday datetime,
@threedaysago datetime,@SubAMount int,@HisAmount int
set @yesterday=DATEADD(day,-1,getdate())
set @beforeyesterday=DATEADD(day,-2,getdate())
set @threedaysago=DATEADD(day,-3,getdate())
set @ToDay =GETDATE()

--做判断先查询统计表是否有记录-前一天
select @SubAMount=COUNT(*) from SMS_SubmitRecord where CONVERT(varchar(8),SubmitDate,112)
= CONVERT(varchar(8),DATEADD(day,-1,getdate()),112) 
begin
if(@SubAMount>0)
	begin
	select @HisAmount=COUNT(*) from SMS_HistoryRecord where (HistoryYear+HistoryMonth+HistoryDay)=CONVERT(varchar(8),@yesterday,112) 
	if(@HisAmount=0)
	  EXECUTE SPHistoryReacrdDay @yesterday
	end
select @SubAMount=COUNT(*) from SMS_SubmitRecord where CONVERT(varchar(8),SubmitDate,112)
= CONVERT(varchar(8),DATEADD(day,-2,getdate()),112) 
if(@SubAMount>0)
   begin
   select @HisAmount=COUNT(*) from SMS_HistoryRecord where (HistoryYear+HistoryMonth+HistoryDay)=CONVERT(varchar(8),@beforeyesterday,112) 
     if(@HisAmount=0)
     EXECUTE SPHistoryReacrdDay @beforeyesterday
   end
select @SubAMount=COUNT(*) from SMS_SubmitRecord where CONVERT(varchar(8),SubmitDate,112)
= CONVERT(varchar(8),DATEADD(day,-3,getdate()),112) 
if(@SubAMount>0)
   begin
   select @HisAmount=COUNT(*) from SMS_HistoryRecord where (HistoryYear+HistoryMonth+HistoryDay)=CONVERT(varchar(8),@threedaysago,112) 
     if(@HisAmount=0)
     EXECUTE SPHistoryReacrdDay @threedaysago
   end
select @SubAMount=COUNT(*) from SMS_SubmitRecord where CONVERT(varchar(8),SubmitDate,112)
= CONVERT(varchar(8),DATEADD(day,0,getdate()),112)    
if(@SubAMount>0)
   begin
   select @HisAmount=COUNT(*) from SMS_HistoryRecord where (HistoryYear+HistoryMonth+HistoryDay)=CONVERT(varchar(8),GETDATE(),112) 
     if(@HisAmount=0)
     EXECUTE SPHistoryReacrdDay @ToDay
   end   
end
COMMIT TRAN
SET NOCOUNT OFF --恢复设置

USE [ESMS]
GO
/****** Object:  StoredProcedure [dbo].[usp_SMS_MT]    Script Date: 05/12/2014 11:28:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[usp_SMS_MT]
	@pageIndex int,		--页码
	@pageSize int,		--页容量
	@pageCount int output	--总页数
as
	--总数据数
	declare @count int
	select @count = COUNT(*) from SMS_MT
	--求总共多少页
	set @pageCount = CEILING( @count*1.0/@pageSize)
	select * from 
	(select *,ROW_NUMBER() over(order by INTO_TIME desc) as num from SMS_MT) as t
	where num between (@pageIndex-1)*@pageSize +1 and @pageIndex*@pageSize
	order by INTO_TIME desc