declare @BuildingID int
declare @TimeUnit int
declare @BeginTime datetime
declare @EndTime datetime
set @BuildingID=1
set @BeginTime='2018-1-1'
set @EndTime='2018-8-10'
select GETDATE()
select * from (
SELECT A.[ID]
,A.[WatermeterID]
,A.[ReadTime]
,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber]
FROM [Log_Watermeter] A
inner join (select [WatermeterID],min([ReadTime]) as [ReadTime] from [Log_Watermeter]
where [ReadTime]>=@BeginTime group by WatermeterID) B
on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]
left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID
where C.BuildingID = @BuildingID ) t1
Union
select * from (
SELECT A.[ID]
,A.[WatermeterID]
,A.[ReadTime]
,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber]
FROM [Log_Watermeter] A
inner join (select [WatermeterID],max([ReadTime]) as [ReadTime] from [Log_Watermeter]
where [ReadTime]<=@EndTime group by WatermeterID) B
on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]
left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID
where C.BuildingID = @BuildingID ) t2
实际为 开始时间对应的数值应向前而不是向后取值比较合理，特别是值按时间增长的读数记录。因此改为：
select * from (
SELECT A.[ID]
,A.[WatermeterID]
,A.[ReadTime]
,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber]
FROM [Log_Watermeter] A
inner join (select [WatermeterID],Max([ReadTime]) as [ReadTime] from [Log_Watermeter]
where [ReadTime]<=@BeginTime group by WatermeterID) B
on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]
left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID
where C.BuildingID = @BuildingID ) t1
Union
select * from (
SELECT A.[ID]
,A.[WatermeterID]
,A.[ReadTime]
,A.[ReadNumber]* dbo.Func_GetSignByBit(C.[IsIn]) as [ReadNumber]
FROM [Log_Watermeter] A
inner join (select [WatermeterID],max([ReadTime]) as [ReadTime] from [Log_Watermeter]
where [ReadTime]<=@EndTime group by WatermeterID) B
on A.[WatermeterID]=B.[WatermeterID] and A.[ReadTime]=B.[ReadTime]
left join R_Watermeter_Building C on A.WatermeterID =c.WatermeterID
where C.BuildingID = @BuildingID ) t2
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
