s elect a.code 合同编号,a.htkssj 合同开始日期,a.htzzsj 合同终止日期,a.qzrq 起租日期,a.qdrq 签订日期,a.xmmc 项目名称,a.ldmc 楼栋名称,
a.fjmc 房间名称,a.khmc 客户名称,a.zllb 租赁类别,a.zldj 租金底价,a.zdr 制单人,a.htzt 合同状态,a.htmj 合同面积,a.jsr 经手人,
a.zjze 租金总额,b.glfze 管理费总额,a.htsx 合同属性
f rom
(s elect r.contractcode code,to_char(r.performance_date,'yyyy-mm-dd') htkssj,to_char(r.finish_date,'yyyy-mm-dd') htzzsj,
to_char(r.accrue_date,'yyyy-mm-dd') qzrq,to_char(r.signed_date,'yyyy-mm-dd') qdrq, p.project_name xmmc,b.build_name ldmc,
h.house_name fjmc,h.pk_house house,c.client_name khmc,d.name zllb,r.contracted_rent||'元/天/平米' zldj,s1.name zdr,
rc.name htzt,rh.calculate_area htmj,(case when s.name is not null then s.name else c.client_name end) jsr,
nvl(sum(wbh.accrued_amount),0) zjze，r.cont_attribute htsx
from rem_contract r
left join rem_contract_house rh on rh.pk_bill=r.pk_contract and rh.dr=0
left join res_house h on rh.pk_house=h.pk_house and h.dr=0
left join res_project p on h.pk_project=p.pk_project and p.dr=0
left join res_build b on h.pk_build=b.pk_build and b.dr=0
left join rem_contractstate rc on rc.pk_contractstate=r.pk_contract_status and rc.dr=0
left join lsbd_datadictionary d on d.pk_datadictionary=r.pk_contracttype and d.dr=0
left join cbd_staff s on s.pk_staff=r.renter and s.dr=0
left join cbd_staff s1 on s1.pk_staff=r.pk_prepared and s1.dr=0
left join wy_bd_fmproject wbf on h.pk_project=wbf.pk_project and wbf.dr=0 and wbf.systemtype='租金'
left join wy_bill_housecost wbh on r.pk_contract=wbh.bill_id and wbh.dr=0 and wbh.pk_projectid=wbf.pk_projectid and wbh.pk_house=h.pk_house
left join res_client c on r.pk_client=c.pk_client and c.dr=0
where r.dr=0 and r.isstart='Y'
group by r.contractcode,r.performance_date,r.finish_date,r.accrue_date,p.project_name,b.build_name,h.house_name,h.pk_house,
r.client_name,d.name,r.contracted_rent,s1.name,rc.name,s.name,c.client_name,r.signed_date,rh.calculate_area,r.cont_attribute) a
left join
(s elect r.contractcode code,h.pk_house house,
(case when rh.index_no=1 then nvl(sum(wbh.accrued_amount),0) else 0 end) glfze
from rem_contract r
left join rem_contract_house rh on rh.pk_bill=r.pk_contract and rh.dr=0
left join res_house h on rh.pk_house=h.pk_house and h.dr=0
left join wy_bd_fmproject wbf on h.pk_project=wbf.pk_project and wbf.dr=0 and wbf.systemtype='管理费'
left join wy_bill_housecost wbh on r.pk_contract=wbh.bill_id and wbh.dr=0 and wbh.pk_projectid=wbf.pk_projectid and wbh.pk_house=h.pk_house
where r.dr=0 and r.isstart='Y'
group by r.contractcode,rh.index_no,h.pk_house order by r.contractcode) b
on a.code=b.code and a.house=b.house
order by a.fjmc
加好友 
发短信
Post By：2022/8/19 13:54:00 [只看该作者]
