CREATE PROCEDURE pro_tj_jd @ksshj int,@jsshj int ,@nksshj int
as
delete from tj_jd
begin
begin
insert into tj_jd(合同编号,承租单位或承租人,物业名称,租赁部位,租赁面积,起始日期,终止日期,合同年度租金,月租金)
select 合同编号,承租单位或承租人,物业名称,租赁部位,租赁面积,起始日期,终止日期,年度租金,季月租金 * 3
from hejbxx
where 审核='1'
and 履行结束='0'
end
begin
create table #zj(
合同编号 varchar(20) null,
物业名称 varchar(20) null,
本实季收租金 float null)
insert into #zj(合同编号,物业名称,本实季收租金)
SELECT 合同编号, 物业名称, SUM(本月实收租金)
FROM dbo.wl2
WHERE (租金统计年月 >= @ksshj) AND (租金统计年月 <= @jsshj)
and 审核='1'
GROUP BY 合同编号, 物业名称
begin
declare @bh varchar(20),@mc varchar(20),@zj float
declare zj_cursor cursor for
select 合同编号,物业名称,本实季收租金 from #zj
open zj_cursor
fetch zj_cursor into @bh,@mc,@zj
while(@@fetch_status<> -1)
begin
update tj_jd set [email protected] where [email protected]
fetch zj_cursor into @bh,@mc,@zj
end
IF @@fetch_status= -1
BEGIN
close zj_cursor
deallocate zj_cursor
end
end
end
begin
create table #sd(
合同编号 varchar(20) null,
物业名称 varchar(20) null,
本季实收电费 float null,
本季实收水费 float null)
insert into #sd(合同编号,物业名称,本季实收电费,本季实收水费)
SELECT 合同编号, 物业名称, SUM(本月电费合计) , SUM(本月水费合计)
FROM sd
WHERE (统计年月 >= @ksshj) AND (统计年月 <[email protected])
and 审核='1'
GROUP BY 合同编号, 物业名称
begin
declare @bh1 varchar(20),@mc1 varchar(20) ,@df float,@sf float
declare sd_cursor cursor for
select 合同编号,物业名称,本季实收电费,本季实收水费 from #sd
open sd_cursor
fetch sd_cursor into @bh,@mc,@df,@sf
while(@@fetch_status<> -1)
begin
update tj_jd set [email protected],[email protected] where [email protected]
fetch sd_cursor into @bh,@mc,@df,@sf
end
IF @@fetch_status= -1
BEGIN
close sd_cursor
deallocate sd_cursor
end
end
end
end
begin
create table #zj2(
合同编号 varchar(20) null,
物业名称 varchar(20) null,
本实季收租金 float null)
insert into #zj2(合同编号,物业名称,本实季收租金)
SELECT 合同编号, 物业名称, SUM(本月实收租金)
FROM dbo.wl2
WHERE (租金统计年月 >= @nksshj) AND (租金统计年月 <= @jsshj)
and 审核='1'
GROUP BY 合同编号, 物业名称
begin
declare @bh2 varchar(20),@mc2 varchar(20),@zj2 float
declare zj2_cursor cursor for
select 合同编号,物业名称,本实季收租金 from #zj2
open zj2_cursor
fetch zj2_cursor into @bh2,@mc2,@zj2
while(@@fetch_status<> -1)
begin
update tj_jd set [email protected] where [email protected]
fetch zj2_cursor into @bh2,@mc2,@zj2
end
IF @@fetch_status= -1
BEGIN
close zj2_cursor
deallocate zj2_cursor
end
end
begin
create table #sd2(
合同编号 varchar(20) null,
物业名称 varchar(20) null,
本季实收电费 float null,
本季实收水费 float null)
insert into #sd2(合同编号,物业名称,本季实收电费,本季实收水费)
SELECT 合同编号, 物业名称, SUM(本月电费合计) , SUM(本月水费合计)
FROM sd
WHERE (统计年月 >= @nksshj) AND (统计年月 <[email protected])
and 审核='1'
GROUP BY 合同编号, 物业名称
begin
declare @bh3 varchar(20),@mc3 varchar(20) ,@df3 float,@sf3 float
declare sd3_cursor cursor for
select 合同编号,物业名称,本季实收电费,本季实收水费 from #sd2
open sd3_cursor
fetch sd3_cursor into @bh3,@mc3,@df3,@sf3
while(@@fetch_status<> -1)
begin
update tj_jd set [email protected],[email protected] where [email protected]
fetch sd3_cursor into @bh3,@mc3,@df3,@sf3
end
IF @@fetch_status= -1
BEGIN
close sd3_cursor
deallocate sd3_cursor
end
end
end
end
GO
------解决方案--------------------