--测试数据---
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 行受影响)