计算账龄代码

来源：互联网发布：潜25和潜39 淘宝哪个店编辑：程序博客网时间：2024/06/10 03:06
--测试数据---if object_id('qfhz') is not null drop table qfhzgocreate table qfhz([统计日期] datetime, [客户代码] varchar(8), [当日发生欠费金额] int,--当天交易产生的欠费 [当日收回欠费金额] int,--当天交易收回的欠费 [欠费余额] int--截止到当日结束的总欠费金额 [历史总欠费]-[历史总收回])insert into qfhz  --这张表的记录日期是连续的，下面简单的把有欠费变化的给出，其他的为 日期,客户,0,0,欠费余额 的格式select '2010-01-01','95010101',1000,0,1000 union allselect '2010-01-02','95010101',500,0,1500 union allselect '2010-01-03','95010101',800,1500,800 union allselect '2010-01-04','95010101',200,0,1000 union allselect '2010-01-05','95010101',700,0,1700 union allselect '2010-01-06','95010101',0,800,900 union allselect '2010-01-07','95010101',900,0,1800 union allselect '2010-01-08','95010101',300,500,1600 union allselect '2010-01-09','95010101',400,0,2000 union allselect '2010-01-10','95010101',400,0,2400 union allselect '2010-01-11','95010101',0,2400,0go if object_id('zl') is not null drop table zlgocreate table zl([id] int, [最小日期] int, [最大日期] int )insert into zlselect 1,1,2 union all      --表示的范围段 2天内select 2,3,5 union all      --3到5天select 3,6,8 union all      --6到8天 select 4,9,10 union all     --9到10天select 5,11,360             --10天以上go--希望用上面两张表生成下面这张表，每天执行一次能生成本天的记录即可if object_id('zlqf') is not null drop table zlqfgocreate table zlqf([统计日期] datetime, [客户代码] varchar(8), [账龄代码] int, [欠费余额] int)CREATE TABLE #tmp( [ID]                INT, [统计日期]          DATETIME, [客户代码]          VARCHAR(8), [当日发生欠费金额]  INT, --当天交易产生的欠费 [当日收回欠费金额]  INT, --当天交易收回的欠费 [欠费余额]          INT--截止到当日结束的总欠费金额 [历史总欠费]-[历史总收回])CREATE TABLE #tmpqf( [统计日期]  DateTIME, [客户代码]  VARCHAR(8), [账龄代码]  INT, [欠费余额]  INT, [ID]        INT)INSERT INTO #TMPSELECT ROW_NUMBER() OVER(ORDER BY 统计日期),       *FROM   qfhzDECLARE @CurrentDate  DATETIME,        @i            INT,        @j            INT,        @hk           INT,        @StartDate    DATETIME,        @Chae         INT,        @ZL           INT,        @JK           INT      SET @CurrentDate = '2010-01-01'WHILE (@CurrentDate <= '2010-01-11')BEGIN    SET @I = 0    SET @J = 0    SELECT @i = id,           @hk = [当日收回欠费金额]    FROM   #tmp    WHERE  [统计日期] = @CurrentDate             INSERT INTO #TMPQF            SELECT [统计日期],[客户代码],[账龄代码],[欠费余额],@i+1 FROM #TMPQF A WHERE ID=@i-1             UNION ALL            SELECT @CurrentDate,[客户代码],1,[当日发生欠费金额],@i+1 FROM #tmp WHERE  ID = @i             SELECT @StartDate=MIN([统计日期]) FROM #TMPQF A WHERE ID=@I-1    AND EXISTS (SELECT 1 FROM (SELECT SUM([欠费余额]) AS 'SumMoney' FROM #tmpqf B WHERE B.ID=@I-1 AND B.[统计日期]<=A.[统计日期] GROUP BY [客户代码])a WHERE A.SumMoney>=@hk )      if(@StartDate is not null)   begin   INSERT INTO #TMPQF   SELECT @StartDate,a.[客户代码],a.[账龄代码],b.Money,@i from #tmpqf a ,(   SELECT SUM([欠费余额])-@hk  as 'Money' FROM #tmpqf a WHERE id=@i+1 AND a.[统计日期]<=@StartDate group by [客户代码]) b   WHERE a.id=@i+1 AND a.[统计日期]=@StartDate            UNION ALL            SELECT [统计日期],[客户代码],[账龄代码],[欠费余额],@i FROM #TMPQF A WHERE ID=@i+1 and a.[统计日期]>@StartDate      DELETE             FROM   #tmpqf            WHERE  [欠费余额] = 0 or ID=@i+1                        INSERT INTO zlqf            SELECT @CurrentDate,                   [客户代码],                   b.id,                   [欠费余额]            FROM   #tmpqf a,                   zl b            WHERE  a.ID = @i                   AND b.最小日期 <=DATEDIFF(day,A.统计日期,dateadd(day,1,@CurrentDate))                   AND b.最大日期 >= DATEDIFF(day,A.统计日期,dateadd(day,1,@CurrentDate))   end   else   BEGIN   INSERT INTO #TMPQF   SELECT @CurrentDate,a.[客户代码],a.[账龄代码],b.Money,@i from #tmpqf a ,(   SELECT SUM([欠费余额])-@hk  as 'Money' FROM #tmpqf a WHERE id=@i+1 AND a.[统计日期]<=@CurrentDate group by [客户代码]) b   WHERE a.id=@i+1 AND a.[统计日期]=@CurrentDate         DELETE             FROM   #tmpqf            WHERE  [欠费余额] = 0 or ID=@i+1                        INSERT INTO zlqf            SELECT @CurrentDate,                   [客户代码],                   b.id,                   [欠费余额]            FROM   #tmpqf a,                   zl b            WHERE  a.ID = @i                   AND b.最小日期 <=DATEDIFF(day,A.统计日期,dateadd(day,1,@CurrentDate))                   AND b.最大日期 >= DATEDIFF(day,A.统计日期,dateadd(day,1,@CurrentDate))      end     SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate)END  SELECT [统计日期],[客户代码],[账龄代码],sum([欠费余额])  FROM zlqf GROUP BY [统计日期],[客户代码],[账龄代码] ORDER BY [统计日期],[客户代码],[账龄代码]descDELETE FROM #tmpDELETE FROM #tmpqf  统计日期                    客户代码     账龄代码        ----------------------- -------- ----------- -----------2010-01-01 00:00:00.000 95010101 1           10002010-01-02 00:00:00.000 95010101 1           15002010-01-03 00:00:00.000 95010101 1           8002010-01-04 00:00:00.000 95010101 1           10002010-01-05 00:00:00.000 95010101 2           8002010-01-05 00:00:00.000 95010101 1           9002010-01-06 00:00:00.000 95010101 2           2002010-01-06 00:00:00.000 95010101 1           7002010-01-07 00:00:00.000 95010101 2           9002010-01-07 00:00:00.000 95010101 1           9002010-01-08 00:00:00.000 95010101 2           4002010-01-08 00:00:00.000 95010101 1           12002010-01-09 00:00:00.000 95010101 2           13002010-01-09 00:00:00.000 95010101 1           7002010-01-10 00:00:00.000 95010101 3           4002010-01-10 00:00:00.000 95010101 2           12002010-01-10 00:00:00.000 95010101 1           800(17 行受影响)