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

    xiaoxiao2021-12-12  11

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

    转载请注明原文地址: https://ju.6miu.com/read-899983.html

    最新回复(0)