--测试数据
create table tb (id int, money int ,type varchar(10), starttime int ,endtime int)
insert tb select
1, 10 , 'type1' , 00 , 08 union all select
2, 5 , 'type1' , 08 , 12 union all select
3, 8 , 'type1' , 12 , 13 union all select
4, 4 , 'type1' , 13 , 18 union all select
5, 7 , 'type1' , 18 , 23 union all select
6, 15 , 'type2' , 00 , 12 union all select
7, 12 , 'type2' , 12 , 18
--语句
declare @starttime int, @endtime int
set @starttime=0
set @endtime=15
select type, sum(money * case when endtime<=@endtime then endtime-starttime else @endtime-starttime end)
from tb
where starttime between @starttime and @endtime
group by type
--结果
type
---------- -----------
type1 116
type2 216(所影响的行数为 2 行)
create table tb (id int, money int ,type varchar(10), starttime int ,endtime int)
insert tb select
1, 10 , 'type1' , 00 , 08 union all select
2, 5 , 'type1' , 08 , 12 union all select
3, 8 , 'type1' , 12 , 13 union all select
4, 4 , 'type1' , 13 , 18 union all select
5, 7 , 'type1' , 18 , 23 union all select
6, 15 , 'type2' , 00 , 12 union all select
7, 12 , 'type2' , 12 , 18
--语句
declare @starttime int, @endtime int
set @starttime=0
set @endtime=15
select type, sum(money * case when endtime<=@endtime then endtime-starttime else @endtime-starttime end)
from tb
where starttime between @starttime and @endtime
group by type
--结果
type
---------- -----------
type1 116
type2 216(所影响的行数为 2 行)
insert into #t1 select
1, 10 , 'type1', '00' , '08' union all select
2 , 5 , 'type1' , '08' , '12' union all select
3 , 8 , 'type1' , '12' , '13' union all select
4 , 4 , 'type1' , '13' , '18' union all select
5 , 7 , 'type1' , '18' , '23' union all select
6 , 5 , 'type2' ,'00' , '12' union all select
7 , 2, 'type2' ,'12', '18'declare @starttime varchar(2),@endtime varchar(2)
select @starttime='00',@endtime='15'
select type,成本=sum(case when starttime>@endtime or uptime<@starttime then 0 else
(cast(case when uptime>@endtime then @endtime else uptime end as int)-
cast(case when starttime>@starttime then starttime else @starttime end as int))*money end)
from #t1 group by type
/*(所影响的行数为 7 行)type 成本
---------- -----------
type1 116
type2 66(所影响的行数为 2 行)
*/