declare @StartTime/*起始时间*/ datetime,@EndTime/*终止时间*/ datetime,@customerid/*客户号*/ int, @customerid1/*客户号*/ int,@opcount1 int,@dt1 datetime,@statid1 tinyint, @port1 tinyint,@term1 tinyint,@customerid2/*客户号*/ int,@opcount2 int,@dt2 datetime,@statid2 tinyint, @port2 tinyint,@term2 tinyint
set @StartTime=''''2005-9-1 00:00:00'''' set @EndTime=''''2005-12-31 00:00:00'''' SELECT 卡刷次数.*, 卡刷记录数.卡刷记录数 AS 卡刷记录数 into #temp FROM (SELECT CustomerID, MAX(OpCount) - MIN(OpCount) + 1 AS 卡刷次数 FROM /*刷卡记录*/ (SELECT CustomerID, OpCount, OpDt AS dt FROM T_ConsumeRec UNION ALL SELECT CustomerID, OpCount, cashDt AS dt FROM T_cashRec UNION ALL SELECT CustomerID, OpCount, putoutDt AS dt FROM t_subsidyputout)/*刷卡记录*/ 刷卡记录1 WHERE (dt BETWEEN @StartTime AND @EndTime)/*时间范围*/ GROUP BY CustomerID) 卡刷次数 INNER JOIN (SELECT customerid, COUNT(*) 卡刷记录数 FROM /*刷卡记录*/ (SELECT CustomerID, OpDt AS dt FROM T_ConsumeRec UNION ALL SELECT CustomerID, cashDt AS dt FROM T_cashRec UNION ALL SELECT CustomerID, putoutDt AS dt FROM t_subsidyputout UNION ALL SELECT CustomerID, opDt AS dt FROM t_correct) /*刷卡记录*/ 刷卡记录2 WHERE (dt BETWEEN @StartTime AND @EndTime)/*时间范围*/ GROUP BY customerid) 卡刷记录数 ON 卡刷次数.CustomerID = 卡刷记录数.customerid AND ( 卡刷次数.卡刷次数 <> 卡刷记录数.卡刷记录数 or (卡刷次数.卡刷次数 = 卡刷记录数.卡刷记录数 and exists(SELECT CustomerID FROM /*刷卡记录*/ (SELECT CustomerID, OpCount, OpDt AS dt FROM T_ConsumeRec UNION ALL SELECT CustomerID, OpCount, cashDt AS dt FROM T_cashRec UNION ALL SELECT CustomerID, OpCount, putoutDt AS dt FROM t_subsidyputout)/*刷卡记录*/ 刷卡记录1 WHERE customerid=卡刷次数.CustomerID and (dt BETWEEN @StartTime AND @EndTime)/*时间范围*/ GROUP BY CustomerID,opcount having count(*)>=2)) ) /*显示有问题的明细记录的客户号*/
--插入要处理的代码 declare pk_custid cursor for select customerid from #temp
open pk_custid
fetch next from pk_custid into @customerid while (@@fetch_status=0) begin declare pk_rec cursor for SELECT * FROM (SELECT CustomerID, OpCount, PutOutDt AS dt, StatID, Port, Term FROM T_SubsidyPutOut UNION ALL SELECT CustomerID, OpCount, opDt AS dt, StatID, Port, Term FROM T_ConsumeRec UNION ALL SELECT CustomerID, OpCount, cashDt AS dt, StatID, Port, Term FROM T_Cashrec) 刷卡记录0 where customerid=@customerid and (dt BETWEEN @StartTime AND @EndTime)/*时间范围*/ order by opcount open pk_rec fetch next from pk_rec into @customerid1,@opcount1,@dt1,@statid1,@port1,@term1 fetch next from pk_rec into @customerid2,@opcount2,@dt2,@statid2,@port2,@term2 while (@@fetch_status=0) begin if (@opcount2-@opcount1=1) begin print ''''1'''' --print ''''正常记录:''''+ @customerid1+'''' ''''+@opcount1+'''' ''''+@dt1+'''' ''''+@statid1+'''' ''''+@port1+'''' ''''+@term1 end else if (@opcount2-@opcount1=0) begin insert into new values(@customerid1,@opcount1,@dt1,@statid1,@port1,@term1) insert into new values(@customerid2,@opcount2,@dt2,@statid2,@port2,@term2) --print ''''重复记录1:''''+ @customerid1+'''' ''''+@opcount1+'''' ''''+@dt1+'''' ''''+@statid1+'''' ''''+@port1+'''' ''''+@term1 --print ''''重复记录2:''''+ @customerid2+'''' ''''+@opcount2+'''' ''''+@dt2+'''' ''''+@statid2+'''' ''''+@port2+'''' ''''+@term2 end else begin insert into new values(@customerid1,@opcount1,@dt1,@statid1,@port1,@term1) insert into new values(@customerid2,@opcount2,@dt2,@statid2,@port2,@term2) &nb [1] [2] 下一页 [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|