某一m1卡收费管理系统有问题明细记录数据t-sql排查代码(草稿)
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)
--print '丢失前记录:'+ @customerid1+' '+@opcount1+' '+@dt1+' '+@statid1+' '+@port1+' '+@term1
--print '丢失后记录:'+ @customerid2+' '+@opcount2+' '+@dt2+' '+@statid2+' '+@port2+' '+@term2
end
fetch next from pk_rec into @customerid1,@opcount1,@dt1,@statid1,@port1,@term1
fetch next from pk_rec into @customerid2,@opcount2,@dt2,@statid2,@port2,@term2
end
close pk_rec
deallocate pk_rec
fetch next from pk_custid into @customerid
end
close pk_custid
deallocate pk_custid
--结束处理代码
drop table #temp
