分期收款视图,高手请进!

Question

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
create view v_dd
asselect 房号 ,费用类型,
应收开始 = convert(char(10),dateadd(mm,a.收费周期*pid,a.合同开始),120),
应收截止 = convert(char(10),dateadd(d,-1,dateadd(mm,a.收费周期*(pid+1),a.合同开始)),120),
应收日期 = convert(char(10),case when pid = 0 then 合同开始 
                else dateadd(d,- 提前天数 ,dateadd(mm,a.收费周期*(pid+1),a.合同开始)) end,120),
应收期间金额 = case when round(金额 / ((datediff(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)*(pid +1)< 金额
           then round(金额 / ((datediff(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)
           else 金额 - round(金额 / ((datediff(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)*(pid ) endfrom ta a
left join (select 0 as pid union 
           select 1 union 
           select 2 union 
           select 3 union 
           select 4 union
           select 5 union 
           select 6 union 
           select 7 union
           select 8 union 
           select 9 union 
           select 10 union
           select 11 union 
           select 12 union 
           select 13 union 
           select 14 union
           select 15
           ) c on dateadd(mm,a.收费周期*pid,a.合同开始) < =a.合同截止goselect * from v_dd
/*
房号         费用类型   应收开始       应收截止       应收日期       应收期间金额      
---------- ------ ---------- ---------- ---------- ----------- 
5F0001     租金     2007-01-01 2007-12-31 2007-01-01 20000
5F0001     租金     2008-01-01 2008-12-31 2008-12-27 20000
5F0003     租金     2007-03-20 2007-09-19 2007-03-20 6000
5F0003     租金     2007-09-20 2008-03-19 2008-03-15 6000
5F0003     租金     2008-03-20 2008-09-19 2008-09-15 6000
5F0003     租金     2008-09-20 2009-03-19 2009-03-15 2000
5F0004     租金     2007-05-08 2007-08-07 2007-05-08 7000
5F0004     租金     2007-08-08 2007-11-07 2007-11-03 7000
5F0004     租金     2007-11-08 2008-02-07 2008-02-03 7000
5F0004     租金     2008-02-08 2008-05-07 2008-05-03 7000
5F0004     租金     2008-05-08 2008-08-07 2008-08-03 7000
5F0004     租金     2008-08-08 2008-11-07 2008-11-03 7000
5F0004     租金     2008-11-08 2009-02-07 2009-02-03 7000
5F0004     租金     2009-02-08 2009-05-07 2009-05-03 7000
5F0006     租金     2007-02-03 2007-04-02 2007-02-03 13000
5F0006     租金     2007-04-03 2007-06-02 2007-05-29 13000
5F0006     租金     2007-06-03 2007-08-02 2007-07-29 13000（所影响的行数为 17 行）*/drop table ta
drop view v_dd

Answer 1

如果costtime=0,或者为null,收费期默认为startdate,closedate, 

Answer 2

只需要在以前的视图上加个case when 就好了

Answer 3

create view v_dd
as
select 房号 ,费用类型,
应收开始 = CASE WHEN 收费周期=0 THEN a.合同开始
ELSE
convert(char(10),dateadd(mm,a.收费周期*pid,a.合同开始),120)
END,
应收截止 = CASE WHEN 收费周期=0 THEN a.合同截止
ELSE
convert(char(10),dateadd(d,-1,dateadd(mm,a.收费周期*(pid+1),a.合同开始)),120)
END, 应收日期 = convert(char(10),case when pid = 0 OR 收费周期=0  then 合同开始 
                 else dateadd(d,- 提前天数 ,dateadd(mm,a.收费周期*(pid+1),a.合同开始)) end,120),
应收期间金额 = CASE WHEN 收费周期=0 THEN 金额
ELSE
 CASE WHEN  ROUND(金额 / ((DATEDIFF(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)*(pid +1)< 金额
           then round(金额 / ((datediff(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)
           else 金额 - round(金额 / ((datediff(mm,合同开始,合同截止)+1) / 收费周期 ) -400,-3)*(pid ) end
ENDfrom ta a
left join (
select 0 as pid union 
           select 1 union 
           select 2 union 
           select 3 union 
           select 4 union
           select 5 union 
           select 6 union 
           select 7 union
           select 8 union 
           select 9 union 
           select 10 union
           select 11 union 
           select 12 union 
           select 13 union 
           select 14 union
           select 15
           ) c on dateadd(mm,a.收费周期*pid,a.合同开始) < =a.合同截止 
AND 收费周期>0