34,576
社区成员
发帖
与我相关
我的任务
分享
Sdt f1 Sid
--------------------------------------------- ----------------------------------------------
2010-03-24 09:30:00.000 39099.518750000003 17
2010-03-24 09:30:00.000 961480.0 18
2010-03-24 09:30:00.000 246.81279296874999 19
2010-03-24 10:00:00.000 39099.518750000003 17
2010-03-24 10:00:00.000 961584.0 18
2010-03-24 10:00:00.000 247.5 19
2010-03-24 10:30:00.000 39099.518750000003 17
2010-03-24 10:30:00.000 961688.0 18
2010-03-24 10:30:00.000 247.5 19
2010-03-24 11:00:00.000 39099.518750000003 17
2010-03-24 11:00:00.000 961784.0 18
2010-03-24 11:00:00.000 247.5 19
2010-03-24 11:30:00.000 39100.159375000003 17
2010-03-24 11:30:00.000 961888.0 18
2010-03-24 11:30:00.000 248.18720703125001 19
2010-03-24 12:00:00.000 39100.159375000003 17
2010-03-24 12:00:00.000 961992.0 18
2010-03-24 12:00:00.000 248.18720703125001 19
2010-03-24 12:30:00.000 39100.159375000003 17
2010-03-24 12:30:00.000 962088.0 18
2010-03-24 12:30:00.000 248.18720703125001 19
那就使用存储过程
create proc getDateInfo
(
@StartTime datetime,
@EndDate datetime
)
as
select Sdt,f1,Sid,
(
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '7:00' and '8:30' then '平' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '9:30' and '11:30' then '尖' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '11:30' and '18:00' then '平' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '18:00' and '23:00' then '峰' else
'谷' end
end
end
end) 'Timename'
from
(
select * from #TT
where Sdt between @StartTime and @EndDate
) tt
exec getDateInfo '2010-3-24 7:00','2010-3-24 18:00'
Sdt f1 Sid Timename
----------------------- ----------- ----------- --------
2010-03-24 09:30:00.000 39099 17 尖
2010-03-24 09:30:00.000 399 18 尖
2010-03-24 09:30:00.000 39099 19 尖
2010-03-24 10:00:00.000 99 17 尖
2010-03-24 10:00:00.000 399 17 尖
2010-03-24 10:00:00.000 39099 18 尖
2010-03-24 10:30:00.000 399 19 尖
2010-03-24 10:30:00.000 39099 17 尖
2010-03-24 10:30:00.000 399 18 尖
2010-03-24 11:00:00.000 39099 17 尖
2010-03-24 11:00:00.000 39099 17 尖
2010-03-24 11:00:00.000 399 18 尖
2010-03-24 11:30:00.000 39099 19 尖
2010-03-24 11:30:00.000 39099 17 尖
2010-03-24 11:30:00.000 39099 17 尖
(15 行受影响)
create table #TT
(
Sdt datetime,
f1 int,
Sid int
)
insert into #TT select '2010-03-24 09:30:00.000',39099,17
insert into #TT select '2010-03-24 09:30:00.000',399,18
insert into #TT select '2010-03-24 09:30:00.000',39099,19
insert into #TT select '2010-03-24 10:00:00.000',99,17
insert into #TT select '2010-03-24 10:00:00.000',399,17
insert into #TT select '2010-03-24 10:00:00.000',39099,18
insert into #TT select '2010-03-24 10:30:00.000',399,19
insert into #TT select '2010-03-24 10:30:00.000',39099,17
insert into #TT select '2010-03-24 10:30:00.000',399,18
insert into #TT select '2010-03-24 11:00:00.000',39099,17
insert into #TT select '2010-03-24 11:00:00.000',39099,17
insert into #TT select '2010-03-24 11:00:00.000',399,18
insert into #TT select '2010-03-24 11:30:00.000',39099,19
insert into #TT select '2010-03-24 11:30:00.000',39099,17
insert into #TT select '2010-03-24 11:30:00.000',39099,17
select Sdt,f1,Sid,
(
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '7:00' and '8:30' then '平' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '9:30' and '11:30' then '尖' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '11:30' and '18:00' then '平' else
case when cast(convert(nvarchar(5),Sdt,114) as datetime) between '18:00' and '23:00' then '峰' else
'谷' end
end
end
end) 'Timename'
from
(
select * from #TT
where Sdt between '2010-3-24 7:00' and '2010-3-24 18:00'
) tt
Sdt f1 Sid Timename
----------------------- ----------- ----------- --------
2010-03-24 09:30:00.000 39099 17 尖
2010-03-24 09:30:00.000 399 18 尖
2010-03-24 09:30:00.000 39099 19 尖
2010-03-24 10:00:00.000 99 17 尖
2010-03-24 10:00:00.000 399 17 尖
2010-03-24 10:00:00.000 39099 18 尖
2010-03-24 10:30:00.000 399 19 尖
2010-03-24 10:30:00.000 39099 17 尖
2010-03-24 10:30:00.000 399 18 尖
2010-03-24 11:00:00.000 39099 17 尖
2010-03-24 11:00:00.000 39099 17 尖
2010-03-24 11:00:00.000 399 18 尖
2010-03-24 11:30:00.000 39099 19 尖
2010-03-24 11:30:00.000 39099 17 尖
2010-03-24 11:30:00.000 39099 17 尖
(15 行受影响)
修改下
select Sdt,f1,Sid,
(
case when convert(nvarchar(5),Sdt,114) between '7:00' and '8:30' then '平' else
case when convert(nvarchar(5),Sdt,114) between '9:30' and '11:30' then '尖' else
case when convert(nvarchar(5),Sdt,114) between '11:30' and '18:00' then '平' else
case when convert(nvarchar(5),Sdt,114) between '18:00' and '23:00' then '峰' else
'谷' end
end
end
end) 'Timename'
from
(
select * from stb where Sdt between '2010-3-24 7:00' and '2010-3-24 18:00'
) tt
select Sdt,f1,Sid
(case when convert(nvarchar(5),Sdt,114) between '7:00' and '8:30' then '平' else
case when convert(nvarchar(5),Sdt,114) between '9:30' and '11:30' then '尖' else
case when convert(nvarchar(5),Sdt,114) between '11:30' and '18:00' then '平' else
case when convert(nvarchar(5),Sdt,114) between '18:00' and '23:00' then '峰' else
'谷'
end
end
end
end) 'Timename',
select * from stb where Sdt between '2010-3-24 7:00' and '2010-3-24 18:00'
Sdt f1 Sid Timename
2010-03-24 09:30:00.000 39099.518750000003 17 尖
2010-03-24 09:30:00.000 961480.0 18 尖
2010-03-24 09:30:00.000 246.81279296874999 19 尖
2010-03-24 10:00:00.000 39099.518750000003 17 尖
2010-03-24 10:00:00.000 961584.0 18 尖
2010-03-24 10:00:00.000 247.5 19 尖
2010-03-24 10:30:00.000 39099.518750000003 17 尖
2010-03-24 10:30:00.000 961688.0 18 尖
2010-03-24 10:30:00.000 247.5 19 尖
2010-03-24 11:00:00.000 39099.518750000003 17 尖
2010-03-24 11:00:00.000 961784.0 18 尖
2010-03-24 11:00:00.000 247.5 19 尖
2010-03-24 11:30:00.000 39100.159375000003 17 尖
2010-03-24 11:30:00.000 961888.0 18 尖
2010-03-24 11:30:00.000 248.18720703125001 19 尖
select a.*,(case when convert(varchar(5),sdt,108) between '07:00' and '08:30' then '平'
when convert(varchar(5),sdt,108) between '08:30' and '09:30' then '峰'
when convert(varchar(5),sdt,108) between '09:30' and '11:30' then '尖'
when convert(varchar(5),sdt,108) between '11:30' and '18:00' then '平'
when convert(varchar(5),sdt,108) between '18:00' and '23:00' then '峰'
when convert(varchar(5),sdt,108) between '23:30' and '07:00' then '谷'
end) Timename from sdt a