计算账龄代码

--测试数据---
if object_id('qfhz') is not null drop table qfhz
go
create table qfhz
([统计日期] datetime,
 [客户代码] varchar(8),
 [当日发生欠费金额] int,--当天交易产生的欠费
 [当日收回欠费金额] int,--当天交易收回的欠费
 [欠费余额] int--截止到当日结束的总欠费金额 [历史总欠费]-[历史总收回]
)
insert into qfhz  --这张表的记录日期是连续的，下面简单的把有欠费变化的给出，其他的为 日期,客户,0,0,欠费余额 的格式
select '2010-01-01','95010101',1000,0,1000 union all
select '2010-01-02','95010101',500,0,1500 union all
select '2010-01-03','95010101',800,1500,800 union all
select '2010-01-04','95010101',200,0,1000 union all
select '2010-01-05','95010101',700,0,1700 union all
select '2010-01-06','95010101',0,800,900 union all
select '2010-01-07','95010101',900,0,1800 union all
select '2010-01-08','95010101',300,500,1600 union all
select '2010-01-09','95010101',400,0,2000 union all
select '2010-01-10','95010101',400,0,2400 union all
select '2010-01-11','95010101',0,2400,0
go

 

if object_id('zl') is not null drop table zl
go
create table zl
([id] int,
 [最小日期] int,
 [最大日期] int
 
)
insert into zl
select 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 zlqf
go
create 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 #TMP
SELECT ROW_NUMBER() OVER(ORDER BY 统计日期),
       *
FROM   qfhz

DECLARE @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 [统计日期],[客户代码],[账龄代码]desc

DELETE FROM #tmp
DELETE FROM #tmpqf

 

 

统计日期                    客户代码     账龄代码        
----------------------- -------- ----------- -----------
2010-01-01 00:00:00.000 95010101 1           1000
2010-01-02 00:00:00.000 95010101 1           1500
2010-01-03 00:00:00.000 95010101 1           800
2010-01-04 00:00:00.000 95010101 1           1000
2010-01-05 00:00:00.000 95010101 2           800
2010-01-05 00:00:00.000 95010101 1           900
2010-01-06 00:00:00.000 95010101 2           200
2010-01-06 00:00:00.000 95010101 1           700
2010-01-07 00:00:00.000 95010101 2           900
2010-01-07 00:00:00.000 95010101 1           900
2010-01-08 00:00:00.000 95010101 2           400
2010-01-08 00:00:00.000 95010101 1           1200
2010-01-09 00:00:00.000 95010101 2           1300
2010-01-09 00:00:00.000 95010101 1           700
2010-01-10 00:00:00.000 95010101 3           400
2010-01-10 00:00:00.000 95010101 2           1200
2010-01-10 00:00:00.000 95010101 1           800

(17 行受影响)