SQL回炉系列（五） 为显示曲线图获取不同时间颗粒的数据

在项目中，需要给用户展现某栋建筑的能耗曲线，用户可以选择单个电表，也可以选择总表，可以选择按年逐月、按月逐日或按日逐时。这是典型的数据分析和计算过程。闲话少说，直接上代码：

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'  

• SQL回炉系列（一） 序言
• SQL回炉系列（二） 多表联合查询和排序
• SQL回炉系列（三） 拆分原始数据到对应的月份表中
• SQL回炉系列（四） 删除重复数据
• SQL回炉系列（五） 为显示曲线图获取不同时间颗粒的数据