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]  下一页