实际工作用到的：同店换房时计算老房间费用

需求：
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就是多表关联。正所谓孰能生巧，万变不离其宗！需要在实际工作中多使用，多连汇贯通！