在项目中,需要给用户展现某栋建筑的能耗曲线,用户可以选择单个电表,也可以选择总表,可以选择按年逐月、按月逐日或按日逐时。这是典型的数据分析和计算过程。闲话少说,直接上代码:
USE [PRODMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[getConsumptionChartData]
(
@DateBegin varchar(20),
@DateEnd Varchar(20),
@ty varchar(2), --y代表年,m代表月,d代表日逐时,b代表区间
@backid varchar(3) -- 0 代表总能耗,其它数字代表各个电表
)
as
begin
if @ty='y' -- 按年逐月
begin
if(@backid='0') -- 总能耗
begin
select sum(consumption) ep , sum(price) price, convert(varchar(7),getTime,120) getTime from HourCost
where convert(varchar(4),getTime,120)=convert(varchar(4),@DateBegin,120)
group by convert(varchar(7),getTime,120)
order by gettime
end
else -- 单个房间的能耗
begin
select sum(consumption) ep , sum(price) price, convert(varchar(7),getTime,120) getTime from HourCost
where back=@backid
and convert(varchar(4),getTime,120)=convert(varchar(4),@DateBegin,120)
group by convert(varchar(7),getTime,120)
order by gettime
end
end
if @ty='m' -- 按月逐日
begin
if(@backid='0') -- 总能耗
begin
select sum(consumption) ep , sum(price) price, convert(varchar(10),getTime,120) getTime from HourCost
where convert(varchar(7),getTime,120)=convert(varchar(7),@DateBegin,120)
group by convert(varchar(10),getTime,120)
order by gettime
end
else -- 单个房间的能耗
begin
select sum(consumption) ep , sum(price) price, convert(varchar(10),getTime,120) getTime from HourCost
where back=@backid
and convert(varchar(7),getTime,120)=convert(varchar(7),@DateBegin,120)
group by convert(varchar(10),getTime,120)
order by gettime
end
end
if @ty='d' -- 按日逐时
begin
if(@backid='0') -- 总能耗
begin
select sum(consumption) ep , sum(price) price, convert(varchar(13),getTime,120)+':00:00' getTime from HourCost
where convert(varchar(10),getTime,120)=convert(varchar(10),@DateBegin,120)
group by convert(varchar(13),getTime,120)
order by gettime
end
else -- 单个房间的能耗
begin
select back, consumption ep , convert(varchar(13),getTime,120)+':00:00' gettime, price from HourCost
where back=@backid
and convert(varchar(10),getTime,120)=convert(varchar(10),@DateBegin,120)
order by gettime
end
end
if @ty='b' -- 时间区间
begin
if(@backid='0') -- 总能耗
begin
select convert(varchar(13),getTime,120)+':00:00' gettime, sum(consumption) ep ,sum(price) price from HourCost
where convert(varchar(13),getTime,120)>=convert(varchar(13),@DateBegin,120)
and convert(varchar(13),getTime,120)<=convert(varchar(13),@DateEnd,120)
group by convert(varchar(13),getTime,120)
order by gettime
end
else -- 单个房间
begin
select back, gettime, consumption ep ,price from HourCost
where back=@backid
and convert(varchar(13),getTime,120)>=convert(varchar(13),@DateBegin,120)
and convert(varchar(13),getTime,120)<=convert(varchar(13),@DateEnd,120)
order by gettime
end
END
end
--execute getConsumptionChartData '2014-08','2014-02-15 23:00:00','m','0'
--execute getConsumptionChartData '2014','2014-02-15 23:00:00','y','14'
--execute getConsumptionChartData '2015-03-26','2014-02-15 23:00:00','d','7'
--execute getConsumptionChartData '2015-03-20 02:00:00','2015-03-20 12:00:00','b','6'