分期收款的视图,如何建立?在线等

stallid,costName,startdate,closedate,costtime,preday   ,costprice 
表数据如下 
房号           费用类型   合同开始         合同截止             收费周期(月单位   )   提前天数     金额 
5F0001 租金           2007-01-01   2008-12-31     12                                 5             40000 
stallid,costName,coststart,costclose,paydate,payvalue 
房号           费用类型   费用开始         费用截止               应收日期             应收金额 
5F0001 租金           2007-01-01   2007-12-31     2007-01-01         20000 
5F0001 租金           2008-01-01   2008-12-31     2007-12-27         20000 －－－这是怎么来的

解决方案 »

  1.   

    看是看明白了,不过要我用SQL写还真麻烦.
      

  2.   

    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-03-15   6000 
    5F0003 租金   2008-09-20 2008-11-19  2008-03-15   2000 这个为啥前三笔是6000,最后一笔是2000?(每个月1000?)
      

  3.   

    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 bselect 房号 ,费用类型,
    合同开始 = 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.合同开始)) endfrom 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     租金     2007-08-08 00:00:00.000                                2007-11-08 00:00:00.000                                2007-11-03 00:00:00.000
    5F0004     租金     2007-11-08 00:00:00.000                                2008-02-08 00:00:00.000                                2008-02-03 00:00:00.000
    5F0004     租金     2008-02-08 00:00:00.000                                2008-05-08 00:00:00.000                                2008-05-03 00:00:00.000
    5F0004     租金     2008-05-08 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-03 00:00:00.000
    5F0004     租金     2008-08-08 00:00:00.000                                2008-11-08 00:00:00.000                                2008-11-03 00:00:00.000
    5F0004     租金     2008-11-08 00:00:00.000                                2009-02-08 00:00:00.000                                2009-02-03 00:00:00.000
    5F0004     租金     2009-02-08 00:00:00.000                                2009-05-08 00:00:00.000                                2009-05-03 00:00:00.000
    5F0006     租金     2007-02-03 00:00:00.000                                2007-04-03 00:00:00.000                                2007-02-03 00:00:00.000
    5F0006     租金     2007-04-03 00:00:00.000                                2007-06-03 00:00:00.000                                2007-05-29 00:00:00.000
    5F0006     租金     2007-06-03 00:00:00.000                                2007-08-03 00:00:00.000                                2007-07-29 00:00:00.000*/drop table ta,#
      

  4.   

    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 bselect 房号 ,费用类型,
    合同开始 = 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
    ,
    收费周期  ,提前天数,
    应收金额 = 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 # 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                                12          5           20000
    5F0001     租金     2008-01-01 00:00:00.000                                2009-01-01 00:00:00.000                                2008-12-27 00:00:00.000                                12          5           20000
    5F0003     租金     2007-03-20 00:00:00.000                                2007-09-20 00:00:00.000                                2007-03-20 00:00:00.000                                6           5           6000
    5F0003     租金     2007-09-20 00:00:00.000                                2008-03-20 00:00:00.000                                2008-03-15 00:00:00.000                                6           5           6000
    5F0003     租金     2008-03-20 00:00:00.000                                2008-09-20 00:00:00.000                                2008-09-15 00:00:00.000                                6           5           6000
    5F0003     租金     2008-09-20 00:00:00.000                                2009-03-20 00:00:00.000                                2009-03-15 00:00:00.000                                6           5           2000
    5F0004     租金     2007-05-08 00:00:00.000                                2007-08-08 00:00:00.000                                2007-05-08 00:00:00.000                                3           5           7000
    5F0004     租金     2007-08-08 00:00:00.000                                2007-11-08 00:00:00.000                                2007-11-03 00:00:00.000                                3           5           7000
    5F0004     租金     2007-11-08 00:00:00.000                                2008-02-08 00:00:00.000                                2008-02-03 00:00:00.000                                3           5           7000
    5F0004     租金     2008-02-08 00:00:00.000                                2008-05-08 00:00:00.000                                2008-05-03 00:00:00.000                                3           5           7000
    5F0004     租金     2008-05-08 00:00:00.000                                2008-08-08 00:00:00.000                                2008-08-03 00:00:00.000                                3           5           7000
    5F0004     租金     2008-08-08 00:00:00.000                                2008-11-08 00:00:00.000                                2008-11-03 00:00:00.000                                3           5           7000
    5F0004     租金     2008-11-08 00:00:00.000                                2009-02-08 00:00:00.000                                2009-02-03 00:00:00.000                                3           5           7000
    5F0004     租金     2009-02-08 00:00:00.000                                2009-05-08 00:00:00.000                                2009-05-03 00:00:00.000                                3           5           7000
    5F0006     租金     2007-02-03 00:00:00.000                                2007-04-03 00:00:00.000                                2007-02-03 00:00:00.000                                2           5           13000
    5F0006     租金     2007-04-03 00:00:00.000                                2007-06-03 00:00:00.000                                2007-05-29 00:00:00.000                                2           5           13000
    5F0006     租金     2007-06-03 00:00:00.000                                2007-08-03 00:00:00.000                                2007-07-29 00:00:00.000                                2           5           13000（所影响的行数为 17 行）
    */drop table ta,#
      

  5.   

    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 100　pid = identity(int,0,1) into # from sysobjects a,sysobjects bselect 房号 ,费用类型,
    合同开始 = 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
    ,
    收费周期  ,提前天数,
    应收金额 = 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 # 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       12          5           20000
    5F0001     租金     2008-01-01 00:00:00.000       2009-01-01 00:00:00.000       2008-12-27 00:00:00.000       12          5           20000
    5F0003     租金     2007-03-20 00:00:00.000       2007-09-20 00:00:00.000       2007-03-20 00:00:00.000       6           5           6000
    5F0003     租金     2007-09-20 00:00:00.000       2008-03-20 00:00:00.000       2008-03-15 00:00:00.000       6           5           6000
    5F0003     租金     2008-03-20 00:00:00.000       2008-09-20 00:00:00.000       2008-09-15 00:00:00.000       6           5           6000
    5F0003     租金     2008-09-20 00:00:00.000       2009-03-20 00:00:00.000       2009-03-15 00:00:00.000       6           5           2000
    5F0004     租金     2007-05-08 00:00:00.000       2007-08-08 00:00:00.000       2007-05-08 00:00:00.000       3           5           7000
    5F0004     租金     2007-08-08 00:00:00.000       2007-11-08 00:00:00.000       2007-11-03 00:00:00.000       3           5           7000
    5F0004     租金     2007-11-08 00:00:00.000       2008-02-08 00:00:00.000       2008-02-03 00:00:00.000       3           5           7000
    5F0004     租金     2008-02-08 00:00:00.000       2008-05-08 00:00:00.000       2008-05-03 00:00:00.000       3           5           7000
    5F0004     租金     2008-05-08 00:00:00.000       2008-08-08 00:00:00.000       2008-08-03 00:00:00.000       3           5           7000
    5F0004     租金     2008-08-08 00:00:00.000       2008-11-08 00:00:00.000       2008-11-03 00:00:00.000       3           5           7000
    5F0004     租金     2008-11-08 00:00:00.000       2009-02-08 00:00:00.000       2009-02-03 00:00:00.000       3           5           7000
    5F0004     租金     2009-02-08 00:00:00.000       2009-05-08 00:00:00.000       2009-05-03 00:00:00.000       3           5           7000
    5F0006     租金     2007-02-03 00:00:00.000       2007-04-03 00:00:00.000       2007-02-03 00:00:00.000       2           5           13000
    5F0006     租金     2007-04-03 00:00:00.000       2007-06-03 00:00:00.000       2007-05-29 00:00:00.000       2           5           13000
    5F0006     租金     2007-06-03 00:00:00.000       2007-08-03 00:00:00.000       2007-07-29 00:00:00.000       2           5           13000（所影响的行数为 17 行）
    */drop table ta,#
      

  6.   

    不是正好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 bselect 房号 ,费用类型,
    合同开始 = convert(char(10),dateadd(mm,a.收费周期*pid,a.合同开始),120),
    合同截止 = convert(char(10),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 # c on dateadd(mm,a.收费周期*pid,a.合同开始) < =a.合同截止/*房号         费用类型   合同开始       合同截止       应收日期       收费周期        提前天数        应收金额        
    ---------- ------ ---------- ---------- ---------- ----------- ----------- ----------- 
    5F0001     租金     2007-01-01 2008-01-01 2007-01-01 12          5           20000
    5F0001     租金     2008-01-01 2009-01-01 2008-12-27 12          5           20000
    5F0003     租金     2007-03-20 2007-09-20 2007-03-20 6           5           6000
    5F0003     租金     2007-09-20 2008-03-20 2008-03-15 6           5           6000
    5F0003     租金     2008-03-20 2008-09-20 2008-09-15 6           5           6000
    5F0003     租金     2008-09-20 2009-03-20 2009-03-15 6           5           2000
    5F0004     租金     2007-05-08 2007-08-08 2007-05-08 3           5           7000
    5F0004     租金     2007-08-08 2007-11-08 2007-11-03 3           5           7000
    5F0004     租金     2007-11-08 2008-02-08 2008-02-03 3           5           7000
    5F0004     租金     2008-02-08 2008-05-08 2008-05-03 3           5           7000
    5F0004     租金     2008-05-08 2008-08-08 2008-08-03 3           5           7000
    5F0004     租金     2008-08-08 2008-11-08 2008-11-03 3           5           7000
    5F0004     租金     2008-11-08 2009-02-08 2009-02-03 3           5           7000
    5F0004     租金     2009-02-08 2009-05-08 2009-05-03 3           5           7000
    5F0006     租金     2007-02-03 2007-04-03 2007-02-03 2           5           13000
    5F0006     租金     2007-04-03 2007-06-03 2007-05-29 2           5           13000
    5F0006     租金     2007-06-03 2007-08-03 2007-07-29 2           5           13000（所影响的行数为 17 行）*/drop table ta,# 
      

  7.   


    以上不对,要求是这样的,不是合同开始截止
    房号           费用类型  应收开始         应收截止               应收日期             应收期间金额 
    5F0001 租金           2007-01-01   2007-12-31     2007-01-01         20000 
    5F0001 租金           2008-01-01   2008-12-31     2007-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-03-15         6000 
    5F0003 租金           2008-09-20   2008-11-19     2008-03-15         2000 
      

  8.   

    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 bselect 房号 ,费用类型,
    应收开始 = 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 # c on dateadd(mm,a.收费周期*pid,a.合同开始) < =a.合同截止/*
    房号         费用类型   应收开始       应收截止       应收日期       应收期间金额      
    ---------- ------ ---------- ---------- ---------- ----------- 
    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,#
      

  9.   

    视图如下：注意修改那个union 形成的表
    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