34,588
社区成员
发帖
与我相关
我的任务
分享
select MAX(
case
when GETDATE() between c1 and c2 then 1
when GETDATE() between c2 and c3 then 2
when f1=0 and GETDATE() between g1 and g2 then 3
when f1=0 and GETDATE() between g2 and g3 then 4
else 0 end
) as [state] from [test] where n=1
--基本思路貌似就是这样的,楼主把getdate()里的time段取出来去比较就好了
CREATE TABLE [Test](
[Id] [int] NOT NULL primary key,
[c1] varchar(7) NOT NULL,
[c2] varchar(7) NOT NULL,
[c3] varchar(7) NOT NULL,
[f1] [tinyint] NOT NULL,
[g1] varchar(7) NOT NULL,
[f2] [tinyint] NOT NULL,
[g2] varchar(7) NOT NULL,
[f3] [tinyint] NOT NULL,
[g3] varchar(7) NOT NULL,
[n] [int] NOT NULL
)
insert into Test(id,c1,c2,c3,f1,g1,f2,g2,f3,g3,n)
select 1,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',1 union all
select 2,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'17:59',1 union all
select 3,'20:10','20:30','20:59',1,'2:10',1,'2:30',1,'5:00',1 union all
select 4,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',2 union all
select 5,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'19:59',2
select * from test
declare @time int
declare @n int
set @n=1
set @time =74000;
with cte as
(
select
id,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c1)as c1,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c2)as c2,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c3)as c3,
f1,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g1)as g1,
f2,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g2)as g2,
f3,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g3)as g3,
n
from test
)
select
max(
case when @time between c1 and c2 then '1'+ cast(f1 as varchar(1))
when @time between c2 and c3 then '2,'+ cast(f1 as varchar(1))
when @time between g1 and g2 then '3'+ cast(f1 as varchar(1))
when @time between g2 and g3 then '4'+ cast(f1 as varchar(1))
else '0' end
)as flag
from cte where n=@n
--flag 前面是输出的 后面是 f1
declare @dt datetime
set @dt='2018-9-1 8:20'
select value
from(
select id,c1,c2,n,1 as value from test
union all
select id,c2,c3,n,2 from test
union all
select id,g1,g2,n,3 from test
union all
select id,g2,g3,n,4 from test
) t
where convert(varchar(5),@dt,108) between c1 and c2
and n=1
declare @date datetime set @date='2018-9-1 8:20'
declare @n int set @i=1
select max(case
when @date between c1 and c2 then 1
when @date between c2 and c3 then 2
when @date between g1 and g2 then 3
end) from [Test] where f1=0 and n=@i
select max(case
when getdate() between c1 and c2 then 1
when getdate() between c2 and c3 then 2
when getdate() between g1 and g2 then 3
end) from [Test] where f1=0
dateadd(dd,1,c2)
declare @dt datetime
set @dt='2013-3-3 4:00'
select value
from(
select id,0 as f1,c1,0 as f2,c2,n,1 as value from test
union all
select id,0,c2,0,c3,n,2 from test
union all
select id,f1,g1,f2,g2,n,3 from test
union all
select id,f2,g2,f3,g3,n,4 from test
) t
where datediff(mi,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(10),c2,120)) else c2 end,convert(varchar(5),@dt,108))<=0
and datediff(mi,c1,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(5),@dt,108)) else convert(varchar(5),@dt,108) end)>=0
and n=3
declare @dt datetime
set @dt='2013-3-3 4:00'
select value
from(
select id,0 as f1,c1,0 as f2,c2,n,1 as value from test
union all
select id,0,c2,0,c3,n,2 from test
union all
select id,f1,g1,f2,g2,n,3 from test
union all
select id,f2,g2,f3,g3,n,4 from test
) t
where datediff(mi,case when f1=0 and f2=1 then dateadd(dd,1,c2) else c2 end,convert(varchar(5),@dt,108))<=0
and datediff(mi,c1,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(5),@dt,108)) else convert(varchar(5),@dt,108) end)>=0
and n=3
/**
value
-----------
4
(1 行受影响)
**/