TABLE1
名称 登记日期 使用时间(年) 使用时间(月) 金额
A 2007-11-26 2 4 300
B 2008-2-10 5 1 200
C 2007-10-30 0 2 400
.
.
.
N1 2008-1-11 6 6 500金额计算是从登记时间的下个自然月起,每月减100,直至减完。
根据输入一个 有效的时间例如 2008-3-20 要求添加的记录是
TABLE2
ID(自动递增) 名称 年 月 金额
1 A 2007 12 200
2 A 2008 1 100
3 A 2008 2 0
3 A 2008 3 0
4 b 2008 3 100
5 c 2007 11 300
6 c 2007 12 200
7 c 2008 1 100
8 c 2008 2 0
9 c 2008 3 0
.
.
.
. n1 2008 2 400
. n1 2008 3 300TABLE2 记录不可重复添加,名称, 年,月 相同就不insert,
例如 1 A 2007 12 只有 一条记录
名称 登记日期 使用时间(年) 使用时间(月) 金额
A 2007-11-26 2 4 300
B 2008-2-10 5 1 200
C 2007-10-30 0 2 400
.
.
.
N1 2008-1-11 6 6 500金额计算是从登记时间的下个自然月起,每月减100,直至减完。
根据输入一个 有效的时间例如 2008-3-20 要求添加的记录是
TABLE2
ID(自动递增) 名称 年 月 金额
1 A 2007 12 200
2 A 2008 1 100
3 A 2008 2 0
3 A 2008 3 0
4 b 2008 3 100
5 c 2007 11 300
6 c 2007 12 200
7 c 2008 1 100
8 c 2008 2 0
9 c 2008 3 0
.
.
.
. n1 2008 2 400
. n1 2008 3 300TABLE2 记录不可重复添加,名称, 年,月 相同就不insert,
例如 1 A 2007 12 只有 一条记录
-- Author: happyflsytone
-- Date:2008-10-20 17:42:06
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(名称 NVARCHAR(1),登记日期 SMALLDATETIME,使用时间y INT,使用时间d INT,金额 INT)
Go
INSERT INTO ta
SELECT 'A','2007-11-26',2,4,300 UNION ALL
SELECT 'B','2008-2-10',5,1,200 UNION ALL
SELECT 'C','2007-10-30',0,2,400
GO
--Start
;with t
as(
select * from ta
union all
select 名称,dateadd(m,1,登记日期) as 登记日期,datepart(yy,dateadd(m,1,登记日期)) as y,
datepart(mm,dateadd(m,1,登记日期)) as m,金额 - 100 as 金额
from t where 金额 > 0
)
SELECT *
FROM t
where 使用时间y > 2000
order by 1,2
--Result:
/*
名称 登记日期 使用时间y 使用时间d 金额
---- ----------------------- ----------- ----------- -----------
A 2007-12-26 00:00:00 2007 12 200
A 2008-01-26 00:00:00 2008 1 100
A 2008-02-26 00:00:00 2008 2 0
B 2008-03-10 00:00:00 2008 3 100
B 2008-04-10 00:00:00 2008 4 0
C 2007-11-30 00:00:00 2007 11 300
C 2007-12-30 00:00:00 2007 12 200
C 2008-01-30 00:00:00 2008 1 100
C 2008-02-29 00:00:00 2008 2 0*/
--End
declare @tb table(名称 varchar(2), 登记日期 smalldatetime, 使用时间年 int, 使用时间月 int, 金额 int)
insert @tb
SELECT 'A', '2007-11-26', 2, 4, 300 UNION ALL
SELECT 'B', '2008-2-10', 5, 1, 200 UNION ALL
SELECT 'C', '2007-10-30', 0, 2, 400declare @num char(100)
select @num=rtrim(sum(使用时间月)) from @tb
exec('select top '+@num+' seq=identity(int,1,1) into temtable from sysobjects,syscolumns')select id=identity(int,1,1),名称,year(dateadd(mm,seq, 登记日期)) as year,month(dateadd(mm,seq, 登记日期)) as month,amt=case when 金额-100*seq>0 then 金额-100*seq else 0 end
into #
from temtable,@tb
where 使用时间月>=seq and 登记日期<='2008-3-20'select * from #
drop table temtable
drop table #
/*
id 名称 year month amt
----------- ---- ----------- ----------- -----------
1 A 2007 12 200
2 A 2008 1 100
3 A 2008 2 0
4 A 2008 3 0
5 B 2008 3 100
6 C 2007 11 300
7 C 2007 12 200
*/
------------------------------------
-- Author: happyflsytone
-- Date:2008-10-20 17:42:06
-------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(名称 NVARCHAR(1),登记日期 SMALLDATETIME,使用时间y INT,使用时间d INT,金额 INT)
Go
INSERT INTO ta
SELECT 'A','2007-11-26',2,4,300 UNION ALL
SELECT 'B','2008-2-10',5,1,200 UNION ALL
SELECT 'C','2007-10-30',0,2,400
GO
--Start
;with t
as(
select * from ta where 登记日期 <= '2008-3-20'
union all
select 名称,dateadd(m,1,登记日期) as 登记日期,datepart(yy,dateadd(m,1,登记日期)) as y,
datepart(mm,dateadd(m,1,登记日期)) as m,case when 金额 - 100 > 0 then 金额 - 100 else 0 end as 金额
from t where convert(char(7),dateadd(m,1,登记日期),120) <= '2008-03')
SELECT *
FROM t
where 使用时间y > 2000
order by 1,2/*
名称 登记日期 使用时间y 使用时间d 金额
---- ----------------------- ----------- ----------- -----------
A 2007-12-26 00:00:00 2007 12 200
A 2008-01-26 00:00:00 2008 1 100
A 2008-02-26 00:00:00 2008 2 0
A 2008-03-26 00:00:00 2008 3 0
B 2008-03-10 00:00:00 2008 3 100
C 2007-11-30 00:00:00 2007 11 300
C 2007-12-30 00:00:00 2007 12 200
C 2008-01-30 00:00:00 2008 1 100
C 2008-02-29 00:00:00 2008 2 0
C 2008-03-29 00:00:00 2008 3 0(10 行受影响)*/
declare @TABLE1 table (名称 varchar(2),登记日期 datetime,[使用时间(年)] int,[使用时间(月)] int,金额 int)
insert into @TABLE1
select 'A','2007-11-26',2,4,300 union all
select 'B','2008-2-10',5,1,200 union all
select 'C','2007-10-30',0,2,400 union all
select 'N1','2008-1-11',6,6,500
declare @end datetime
set @end='2008-3-20'
select top 8000 id=identity(int,1,1) into # from syscolumns
select 名称,年=datepart(yy,dateadd(mm,b.id,登记日期)),月=datepart(mm,dateadd(mm,b.id,登记日期)),金额=case when 金额-b.id*100>0 then 金额-b.id*100 else 0 end
from @TABLE1 a,# b where datediff(mm,登记日期,@end)>=b.id--结果:
名称 年 月 金额
---- ----------- ----------- -----------
A 2007 12 200
A 2008 1 100
A 2008 2 0
A 2008 3 0
B 2008 3 100
C 2007 11 300
C 2007 12 200
C 2008 1 100
C 2008 2 0
C 2008 3 0
N1 2008 2 400
N1 2008 3 300
insert @tb
SELECT 'A', '2007-11-26', 2, 4, 300 UNION ALL
SELECT 'B', '2008-2-10', 5, 1, 200 UNION ALL
SELECT 'C', '2007-10-30', 0, 2, 400declare @mindate smalldatetime
select @mindate=(select min(登记日期) from @tb)
declare @date table(date smalldatetime, seq int)
declare @seq int
set @seq=0
while datediff(mm,@mindate,'2008-3-20')>=0
begin
set @seq=@seq+1
insert @date select @mindate,@seq
set @mindate=dateadd(month, 1, @mindate)
end
select id=identity(int,1,1),名称,year(dateadd(mm,seq,登记日期)) as year,month(dateadd(mm,seq,登记日期)) as mth,amt=case when 金额-100*seq>0 then 金额-100*seq else 0 end
into #
from @date,@tb
where 登记日期<='2008-3-20' and year(dateadd(mm,seq,登记日期))*12+month(dateadd(mm,seq,登记日期))<=2008*12+3select * from #
drop table #
/*
id 名称 year mth amt
----------- ---- ----------- ----------- -----------
1 A 2007 12 200
2 A 2008 1 100
3 A 2008 2 0
4 A 2008 3 0
5 B 2008 3 100
6 C 2007 11 300
7 C 2007 12 200
8 C 2008 1 100
9 C 2008 2 0
10 C 2008 3 0
*/
declare @tb table(名称 varchar(2), 登记日期 smalldatetime, 使用时间年 int, 使用时间月 int, 金额 int)
insert @tb
SELECT 'A', '2007-11-26', 2, 4, 300 UNION ALL
SELECT 'B', '2008-2-10', 5, 1, 200 UNION ALL
SELECT 'C', '2007-10-30', 0, 2, 400declare @mindate smalldatetime
select @mindate=(select min(登记日期) from @tb)
declare @date table(date smalldatetime, seq int)
declare @seq int
set @seq=0
while datediff(mm,@mindate,'2008-3-20')>=0
begin
set @seq=@seq+1
insert @date select @mindate,@seq
set @mindate=dateadd(month, 1, @mindate)
end
select id=identity(int,1,1),名称,year(dateadd(mm,seq,登记日期)) as year,month(dateadd(mm,seq,登记日期)) as mth,amt=case when 金额-100*seq>0 then 金额-100*seq else 0 end
into #
from @date,@tb
where 登记日期 <='2008-3-20' and year(dateadd(mm,seq,登记日期))*12+month(dateadd(mm,seq,登记日期)) <=2008*12+3select * from #
drop table #
/*
id 名称 year mth amt
----------- ---- ----------- ----------- -----------
1 A 2007 12 200
2 A 2008 1 100
3 A 2008 2 0
4 A 2008 3 0
5 B 2008 3 100
6 C 2007 11 300
7 C 2007 12 200
8 C 2008 1 100
9 C 2008 2 0
10 C 2008 3 0
*/