有一个表 有两列 开始时间 结束时间 需要知道结束时间减去开始时间看用了多长时间 但是要剔除晚上20:00到早上8:00的这段时间 开始时间和结束时间有可能不是同一天,也有可能是同一天 谁有办法用SQL实现这个东西 帮帮忙 谢谢了
解决方案 »
- 急急急!!!!求sql
- select * into type from table
- oracle 未找到要求的From关键字
- oracle exp/imp问题。新手学习oracle
- 请那位大虾,帮一下小弟吧!!
- 用 blob 字段的数据表应该注意什么,我现在向该字段插入一个 50M 的 Word 文档就报错,说“无法通过128(在表空间STAM中)扩展”
- 关于otn注册的问题(重金)
- 更改字符集数据库不能启动!请高手回答,必送分!
- 请问谁有unxi下安装oracle的资料,现在急须,一定给高分
- 求助,sql 分组条件影响sum 结果
- plsql连接oracle11G出现ORA-12154: TNS: 无法解析指定的连接标识符
- 数据库里储存的是葡文为什么放到txt文件里多了一位
go
create table #m(
begindate datetime,
enddate datetime
)
go
insert #m
select '2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select '2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select '2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select '2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select '2012-03-21 08:56:00.000','2012-03-24 09:56:00.000'select *,(DATEDIFF(HH,begindate,enddate)-DATEDIFF(DD,begindate,enddate)*12) as haoshi
from #m/*
begindate enddate haoshi
2012-01-12 08:56:00.000 2012-01-13 08:56:00.000 12
2012-01-14 08:56:00.000 2012-01-16 06:56:00.000 22
2012-02-12 08:56:00.000 2012-02-13 09:23:00.000 13
2012-03-15 08:56:00.000 2012-03-17 17:04:00.000 33
2012-03-21 08:56:00.000 2012-03-24 09:56:00.000 37
*/
--如果要精确到分
select *,(cast(DATEDIFF(MI,begindate,enddate)as numeric(10,2))/60)
-(DATEDIFF(DD,begindate,enddate)*12) as haoshi from #m/*
begindate enddate haoshi
2012-01-12 08:56:00.000 2012-01-13 08:56:00.000 12.000000
2012-01-14 08:56:00.000 2012-01-16 06:56:00.000 22.000000
2012-02-12 08:56:00.000 2012-02-13 09:23:00.000 12.450000
2012-03-15 08:56:00.000 2012-03-17 17:04:00.000 32.133333
2012-03-21 08:56:00.000 2012-03-24 09:56:00.000 37.000000
*/
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([begindate] datetime,[enddate] datetime)
insert [tbl]
select '2012-02-04 18:00','2012-02-05 9:00' union all
select '2012-02-04 15:00','2012-02-04 22:00' union all
select '2012-02-04 18:00','2012-02-05 07:00' union all
select '2012-02-05 8:00','2012-02-05 23:00'--如果要精确到分
select *,
case when day([begindate])<>day([enddate]) and DATEPART(HH,[enddate])>=8
then (DATEDIFF(HH,begindate,enddate))-(DATEDIFF(DD,begindate,enddate)*12)
when day([begindate])<>day([enddate]) and DATEPART(HH,[enddate])<8
or (day([begindate])=day([enddate]) and DATEPART(HH,[enddate])>=20)
then datediff(HH,begindate,convert(varchar(10),begindate,120)+' 20:00:00')
else DATEDIFF(HH,begindate,enddate)
end as haoshi
from tbl/*
begindate enddate haoshi
2012-02-04 18:00:00.000 2012-02-05 09:00:00.000 3
2012-02-04 15:00:00.000 2012-02-04 22:00:00.000 5
2012-02-04 18:00:00.000 2012-02-05 07:00:00.000 2
2012-02-05 08:00:00.000 2012-02-05 23:00:00.000 12
*/你可以用case when 把所有情况下的算法列出来,然后看看有没有能合并的,在合并就好了
declare @tb table (
begindate datetime,
enddate datetime
)insert into @tb
select '2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select '2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select '2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select '2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select '2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select '2012-03-21 08:56:00.000','2012-03-21 09:56:00.000'
select MAX(date)max_long from (
select case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
else 0 end date
from @tb )a
/*
max_long
720
*/
declare @tb table (
id int,
begindate datetime,
enddate datetime
)insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000'
select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*abs(DATEPART(hour,begindate)-DATEPART(hour,enddate)) end
else 0 end logdate
from @tb
/*
id logdate
1 720
2 0
3 720
4 18
5 3
6 60
*/
what is your name ?
前俩个考虑情况不完整。这个是所有情况
declare @tb table (
id int,
begindate datetime,
enddate datetime
)insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000' union all
select 7,'2012-03-23 22:56:00.000','2012-03-25 23:56:00.000'select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end else 0 end logdate
from @tb
/*
id logdate
1 720
2 1560
3 720
4 1980
5 2220
6 60
7 1500
*/
select
max(case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end else 0 end) logdate
from @tb /*
logdate
2220
*/
id int,
begindate datetime,
enddate datetime
)insert into @tb
select 1,'2012-01-12 08:56:00.000','2012-01-13 08:56:00.000' union all
select 2,'2012-01-14 08:56:00.000','2012-01-16 06:56:00.000' union all
select 3,'2012-02-12 08:56:00.000','2012-02-13 09:23:00.000' union all
select 4,'2012-03-15 08:56:00.000','2012-03-17 17:04:00.000' union all
select 5,'2012-03-21 08:56:00.000','2012-03-24 09:56:00.000' union all
select 6,'2012-03-21 08:56:00.000','2012-03-21 09:56:00.000' union all
select 7,'2012-03-23 22:56:00.000','2012-03-25 23:56:00.000'select
id,case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end else 0 end logdate
from @tb
/*
1 720
2 1560
3 720
4 1980
5 2220
6 60
7 1560
*/
select
max(case when DATEPART(HOUR,begindate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=0 then DATEDIFF(mi,begindate,enddate)
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 AND DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) between 8 and 20 and DATEPART(HOUR,enddate)not between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)=1 then case when DATEDIFF(hh,begindate,enddate)>12 then 12*60 else DATEDIFF(hh,begindate,enddate)*60 end
when DATEPART(HOUR,begindate) not between 8 and 20 and DATEPART(HOUR,enddate) between 8 and 20 and DATEDIFF(DD,begindate,enddate)>1 then case when DATEDIFF(hh,begindate,enddate)%12=0 then 12*60*DATEDIFF(dd,begindate,enddate) else DATEDIFF(day,begindate,enddate)*12*60+abs(DATEPART(hour,begindate)-DATEPART(hour,enddate))*60*DATEDIFF(day,begindate,enddate) end else 0 end) logdate
from @tb /*
logdate
2220
*/
go
if object_id('tbl')is not null
drop table tbl
go
create table tbl(
begindate datetime,
enddate datetime
)
go
insert tbl
select '2012-01-01 09:00:00','2012-01-01 19:00:00' union all
select '2012-01-02 09:00:00','2012-01-03 19:00:00' union all
select '2012-01-04 06:00:00','2012-01-04 23:00:00' union all
select '2012-01-05 06:00:00','2012-01-06 23:00:00' union all
select '2012-01-06 23:00:00','2012-01-07 23:00:00' union all--
select '2012-01-08 06:00:00','2012-01-09 06:00:00' union all
select '2012-01-20 23:00:00','2012-01-22 06:00:00' union all
select '2012-01-09 06:00:00','2012-01-09 19:00:00' union all
select '2012-01-10 06:00:00','2012-01-11 19:00:00' union all
select '2012-01-13 23:00:00','2012-01-14 19:00:00' union all
select '2012-01-15 09:00:00','2012-01-15 22:00:00' union all
select '2012-01-16 09:00:00','2012-01-17 22:00:00' union all
select '2012-01-18 09:00:00','2012-01-19 06:00:00'
select *,
case
when (DATEPART(HH,begindate) between 8 and 20) and (DATEPART(HH,enddate) between 8 and 20)
then (DATEDIFF(HH,begindate,enddate)-DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 8 and 20)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',enddate)-DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 8 and 20) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,begindate,convert(varchar(10),enddate,120)+' 20:00:00'))-(DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 0 and 7)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)+1)*12)
when (DATEPART(HH,begindate) between 0 and 7) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,convert(varchar(10),begindate,120)+' 08:00:00',convert(varchar(10),enddate,120)+' 20:00:00'))
-(DATEDIFF(DD,begindate,enddate)*12)
when (DATEPART(HH,begindate) between 21and 23) and (DATEPART(HH,enddate) between 21 and 23)
then (DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 8 and 20 and (DATEPART(HH,enddate) between 0 and 8))
then DATEDIFF(HH,begindate,dateadd(dd,-1,convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 21 and 23 and (DATEPART(HH,enddate) between 8 and 20))
then (DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),enddate))
-((DATEDIFF(DD,begindate,enddate)-1)*12)
when (DATEPART(HH,begindate) between 21 and 23 and (DATEPART(HH,enddate) between 0 and 7))
then DATEDIFF(HH,dateadd(dd,1,convert(varchar(10),begindate,120)+' 08:00:00'),
dateadd(dd,-1,convert(varchar(10),enddate,120)+' 20:00:00'))
-((DATEDIFF(DD,begindate,enddate)-2)*12)
else 0 end as haoshi
from tbl/*
begindate enddate haoshi
2012-01-01 09:00:00.000 2012-01-01 19:00:00.000 10
2012-01-02 09:00:00.000 2012-01-03 19:00:00.000 22
2012-01-04 06:00:00.000 2012-01-04 23:00:00.000 12
2012-01-05 06:00:00.000 2012-01-06 23:00:00.000 24
2012-01-06 23:00:00.000 2012-01-07 23:00:00.000 12
2012-01-08 06:00:00.000 2012-01-09 06:00:00.000 12
2012-01-20 23:00:00.000 2012-01-22 06:00:00.000 12
2012-01-09 06:00:00.000 2012-01-09 19:00:00.000 11
2012-01-10 06:00:00.000 2012-01-11 19:00:00.000 23
2012-01-13 23:00:00.000 2012-01-14 19:00:00.000 11
2012-01-15 09:00:00.000 2012-01-15 22:00:00.000 11
2012-01-16 09:00:00.000 2012-01-17 22:00:00.000 23
2012-01-18 09:00:00.000 2012-01-19 06:00:00.000 11
*/我能想到的情况处理完了,你可以测试一下,有问题告诉我