关于电费计算的SQL语句

[just59277]

现在已知每小时的电能耗，要根据电能耗计算电费，由于电是分时段计费的（峰谷平三个时段收费不一样）
另有一张电价信息表，里面有 峰谷平时段电价等信息
要算出对应的电费 sql语句怎么写

电能耗表结构
BuildingID time value

电价信息表
BuildingID time fengValue guValue pingValue

电价信息同一个建筑不同时段可能值不同


大致结构如此，求帮助

[黄_瓜]

有的数据就好办多了

[--小F--]

列转行吗？需要得到的结果是什么？

[just59277]

需要的结果是某一段时间内的电费 可以选择按月或者按天计算

[--小F--]

select
   a.BuildingID,
   sum(case when time between 'xx' and 'xx' then fengValue 
            when time between 'xx' and 'xx' then guValue
            when time between 'xx' and 'xx' then pingValue

        end)*a.value
from
   电能耗表 a join 电价信息表 b 
on
  a.BuildingID=b.BuildingID
group by
  a.BuildingID

[--小F--]

select
   a.BuildingID,
   sum(case when time between 'xx' and 'xx' then fengValue 
            when time between 'xx' and 'xx' then guValue
            when time between 'xx' and 'xx' then pingValue

        end)*a.value
from
   电能耗表 a join 电价信息表 b 
on
  a.BuildingID=b.BuildingID
where
  a.time between 'xx' and 'xx'
group by
  a.BuildingID

[just59277]

[Quote=引用 5 楼 fredrickhu 的回复:]

SQL code
select
a.BuildingID,
sum(case when time between 'xx' and 'xx' then fengValue
when time between 'xx' and 'xx' then guValue
when time between 'xx' and 'xx' then……
[/Quote]

这个我考虑过 问题是 有可能 这个时间段内 有多种价格 你这样算就不准了

[--小F--]

请给出测试数据和所需要的结果。

[q465897859]

无数据无真相

[just59277]

BuildingID time value
1 2011/4/30 23:00 1
1 2011/5/1 00:00 1


BuildingID time fengValue guValue pingValue
1 2011/1/1 1 2 3
1 2011/5/1 2 4 6


查询条件 BuildingID 时间段 2011/4/1-2011/6/1
结果
BuildingID time cost
1 2011/4/30 23:00 2
1 2011/5/1 00:00 4

这里只取了谷时段的数据例子 实际数据里面还要分其他的时段计算

[Mr_Nice]

if object_id('[TB_1]') is not null drop table [TB_1]
go
create table [TB_1] (BuildingID int,time datetime,value int)
insert into [TB_1]
select 1,'2011/4/30 23:00',1 union all
select 1,'2011/5/1 00:00',1

if object_id('[TB_2]') is not null drop table [TB_2]
go
create table [TB_2] (BuildingID int,time datetime,fengValue int,guValue int,pingValue int)
insert into [TB_2]
select 1,'2011/1/1',1,2,3 union all
select 1,'2011/5/1',2,4,6

select * from [TB_1]
select * from [TB_2]


SELECT  A.BuildingID ,
        A.time AS 日期时间 ,
        MAX(B.time) AS 收费时限 ,
        ( CASE WHEN CONVERT(TIME, A.[time]) BETWEEN '5:00' AND '9:00'   --时间自己调整一下
               THEN MAX(B.fengValue)
               ELSE CASE WHEN CONVERT(TIME, A.[time]) BETWEEN '16:00' AND '23:59'
                         THEN MAX(B.guValue)
                         ELSE CASE WHEN CONVERT(TIME, A.[time]) BETWEEN '9:00' AND '16:00'
                                   THEN MAX(B.pingValue)
                                   ELSE CASE WHEN CONVERT(TIME, A.[time]) BETWEEN '0:00' AND '5:00'
                                             THEN MAX(B.guValue)
                                        END
                              END
                    END
          END ) * A.value AS cost
FROM    [TB_1] A
        INNER JOIN [TB_2] B ON A.time >= B.time
GROUP BY A.BuildingID ,
        A.time ,
        A.value

/*
BuildingID  日期时间                    收费时限                    cost
----------- ----------------------- ----------------------- -----------
1           2011-04-30 23:00:00.000 2011-01-01 00:00:00.000 2
1           2011-05-01 00:00:00.000 2011-05-01 00:00:00.000 4

(2 行受影响)*/

[just59277]

自己解决了 跟10楼的差不多 具体代码我就不贴了 谢谢大家