日期:2014-05-18 浏览次数:20729 次
create table ta(房号 varchar(10),费用类型 varchar(6), 合同开始 datetime,合同截止 datetime,收费周期 int,提前天数 int,金额 int) insert ta select '5F0001','租金','2007-01-01','2008-12-31',12,5 , 40000 union select '5F0003','租金','2007-03-20','2008-11-19',6,5 , 20000 union select '5F0004','租金','2007-05-08','2009-04-30',3,5 , 60000 union select '5F0006','租金','2007-02-03','2007-08-02',2,5 , 40000 go select top 20 pid = identity(int,0,1) into # from sysobjects a,sysobjects b select 房号 ,费用类型, 合同开始 = dateadd(mm,a.收费周期*pid,a.合同开始), 合同截止 = dateadd(mm,a.收费周期*(pid+1),a.合同开始), 应收日期 = case when pid = 0 then 合同开始 else dateadd(d,- 提前天数 ,dateadd(mm,a.收费周期*(pid+1),a.合同开始)) end from ta a left join # c on dateadd(mm,a.收费周期*pid,a.合同开始) < =a.合同截止 /* 房号 费用类型 合同开始 合同截止 应收日期 ---------- ------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ 5F0001 租金 2007-01-01 00:00:00.000 2008-01-01 00:00:00.000 2007-01-01 00:00:00.000 5F0001 租金 2008-01-01 00:00:00.000 2009-01-01 00:00:00.000 2008-12-27 00:00:00.000 5F0003 租金 2007-03-20 00:00:00.000 2007-09-20 00:00:00.000 2007-03-20 00:00:00.000 5F0003 租金 2007-09-20 00:00:00.000 2008-03-20 00:00:00.000 2008-03-15 00:00:00.000 5F0003 租金 2008-03-20 00:00:00.000 2008-09-20 00:00:00.000 2008-09-15 00:00:00.000 5F0003 租金 2008-09-20 00:00:00.000 2009-03-20 00:00:00.000 2009-03-15 00:00:00.000 5F0004 租金 2007-05-08 00:00:00.000 2007-08-08 00:00:00.000 2007-05-08 00:00:00.000 5F0004 租金