需求:
1. 租房时,房间由于其他原因,需要换房
2. 老房间的费用需要进行结算:2016-11-15开始换房,老房间2017-02-20房间到期,房费缴纳到2016-12-19
3. 换房时,需要先退还2016-11-15~2016-12-19日的房费/服务费,并对已用的水电费进行结算
主要用到的思想:
1. 先将水电费手动计算并插入到临时表1中;
2. 然后查询页面上所展示出来的数据并插入到临时表2中;
3. 进行对比结果并判断结果;
4. 丢弃临时表。
如下:
/*
select ContractID,DayRentPrice,DayServiceFee,* from ContractCalendar
where ContractID = 'R150040218'
and BizDate>='2016-10-25'
and InputBillTag = 1
order by BizDate desc
select 83.870968*31 + 86.666667*4
*/
declare @ContractID varchar(50)
declare @contractroomid varchar(50)
declare @date date
set @ContractID = 'R150038016'
set @contractroomid = 'R150038016C001'
Select @date = Convert(char(10),getdate(),126)
--老房间押金
select Deposit from ContractRoom
where ContractID = @ContractID
--计算老房间剩余房费和服务费
select SUM(DayRentPrice) sumDayRent,SUM(DayServiceFee) sumDayService,ContractID
from ContractCalendar
where ContractID = @ContractID
and BizDate>=@date --换房日
and InputBillTag = 1
group by ContractID
--手动计算水电费用
declare @E numeric(14,5)
declare @CW numeric(14,5)
declare @HW numeric(14,5)
SET @E= (select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10501')
SET @CW=(select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10502')
SET @HW=(select StoreFeePrice from ContractFeeConfig where ContractID = @ContractID and AccountSubjectCode = '10503')
--select convert (numeric(14,5)
--,coalesce(storefeeprice,0)) as price,* from ContractFeeConfig
--where ContractID = 'R160002722' and AccountSubjectCode in ('10501','10502','10503')
declare @el1 decimal(10,2)
declare @wc1 decimal(10,2)
declare @wh1 decimal(10,2)
select
cast(电表使用量*@E as decimal(10,2)) as '电费',
cast(冷水使用量*@CW as decimal(10,2)) as '冷水费',
cast(热水使用量*@HW as decimal(10,2)) as '热水费'
into #temp1
from
(
select top 1
ElectricReading as '电初始读数',ColdWaterReading as '冷水初始读数',HotWaterReading as '热水初始读数',
ElectricReadingNew as '电最新读数',ColdWaterReadingNew as '冷水最新读数',HotWaterReadingNew as '热水最新读数'
,(ElectricReadingNew-ElectricReading) as '电表使用量',(ColdWaterReadingNew-ColdWaterReading) as '冷水使用量'
,(HotWaterReadingNew-HotWaterReading) as '热水使用量'
from Bill b
inner join ContractFeeConfig cfc on cfc.ContractID = b.ContractID
where b.ContractID = @ContractID
) a
select 电费,冷水费,热水费 from #temp1
select @el1 = 电费 from #temp1
select @wc1 = 冷水费 from #temp1
select @wh1 = 热水费 from #temp1
--页面显示的水电费用
select
ele.ContractRoomID
,ele.RoomNo
,isnull(bb.Amount,0) Amount
,ele.AccountSubjectCode
,cl2.CounterReading InitReading
,cl.CounterReading LastReading
,cast(cl2.CounterReading as varchar(10))+'到'+cast(cl.CounterReading as varchar(10)) Remark
,(cl.CounterReading-cl2.CounterReading) UsedReading
,cast((cl.CounterReading-cl2.CounterReading)*isnull(cff.StoreFeePrice,0) as decimal(10,2)) UsedAmount
into #temp2
from
(
select
StoreID
,ContractRoomID
,RoomNo
,AccountSubjectCode
,MAX(SequenceID)MaxSequenceID
,MIN(SequenceID)MinSequenceID
from CounterLog
where contractroomid=@contractroomid
group by StoreID,ContractRoomID,RoomNo,AccountSubjectCode
) Ele
left join CounterLog Cl
on ele.ContractRoomID=cl.ContractRoomID
and ele.RoomNo=cl.RoomNo
and ele.AccountSubjectCode=cl.AccountSubjectCode
and ele.MaxSequenceID=cl.SequenceID
left join CounterLog Cl2
on ele.ContractRoomID=Cl2.ContractRoomID
and ele.RoomNo=Cl2.RoomNo
and ele.AccountSubjectCode=Cl2.AccountSubjectCode
and ele.MinSequenceID=Cl2.SequenceID
left join contractfeeconfig cff
on ele.ContractRoomID=cff.ContractID+'C001'
and ele.AccountSubjectCode=cff.AccountSubjectCode
left join
(select ContractRoomID,isnull(sum(Amount),0) Amount
from BillDetail
where AccountSubjectcode in('10501','10502','10503','10504')
group by ContractRoomID
) bb
on ele.ContractRoomID=bb.ContractRoomID
select * from #temp2
declare @el2 decimal(10,2)
declare @wc2 decimal(10,2)
declare @wh2 decimal(10,2)
select @el2 = usedamount from #temp2
where AccountSubjectCode = '10501'
select @wc2 = usedamount from #temp2
where AccountSubjectCode = '10502'
select @wh2 = usedamount from #temp2
where AccountSubjectCode = '10503'
--比较计算出来的水电读数与页面展示数据是否一致
if @el1 = @el2
print '电费计算正确'
else
print '电费计算错误'
if @wc1 = @wc2
print '冷水费计算正确'
else
print '冷水费计算错误'
if @wh1 = @wh2
print '热水费计算正确'
else
print '热水费计算错误'
drop table #temp1
drop table #temp2
主要用到的sql就是多表关联。正所谓孰能生巧,万变不离其宗!需要在实际工作中多使用,多连汇贯通!