项目查询每小时显示班次的问题 [英] problem with project-query for hourly display of shift
问题描述
我希望查询从班次计时每小时生产的显示。
示例:班次时间是8:00:00到20:00:00.i想要显示8-9,9-10 ,10-11喜欢这个。请帮助我吗?
制作表
recordno startrtc endrtc
'69 ','2014-08-18 09:47:52','2014-08-18 09:47:53',
'70','2014-08-18 09:47: 58','2014-08-18 09:47:59',
'71','2014-08-18 09:48:01','2014-08-18 09:48 :02',
'72','2014-08-18 09:48:04','2014-08-18 09:48:05',
'73','2014-08-18 09:48:07','2014-08-18 09:48:08',
'74','2014-08-18 09: 48:11','2014-08-18 09:48:12',
'75','2014-08-18 09:48:19','2014-08-18 09 :48:20',
'76','2014-08-18 09:48:23','2014-08-18 09:48:24',
'77','2014-08-18 09:48:32','2014-08-18 09:48:33',
'78','2014-08-18 09:4 8:44','2014-08-18 09:48:45',
工作表
id jobid jobrtc
1 3'2014-08-17 09:47:58'
2 6'2014-08-18 09:47:58'
i want query for display per hourly production from shift timing .
example: shift timing is 8:00:00 to 20:00:00.i want show 8-9,9-10,10-11 like this.please help me?
production table
recordno startrtc endrtc
'69', '2014-08-18 09:47:52', '2014-08-18 09:47:53',
'70', '2014-08-18 09:47:58', '2014-08-18 09:47:59',
'71', '2014-08-18 09:48:01', '2014-08-18 09:48:02',
'72', '2014-08-18 09:48:04', '2014-08-18 09:48:05',
'73', '2014-08-18 09:48:07', '2014-08-18 09:48:08',
'74', '2014-08-18 09:48:11', '2014-08-18 09:48:12',
'75', '2014-08-18 09:48:19', '2014-08-18 09:48:20',
'76', '2014-08-18 09:48:23', '2014-08-18 09:48:24',
'77', '2014-08-18 09:48:32', '2014-08-18 09:48:33',
'78', '2014-08-18 09:48:44', '2014-08-18 09:48:45',
job table
id jobid jobrtc
1 3 '2014-08-17 09:47:58'
2 6 '2014-08-18 09:47:58'
推荐答案
试试这个,希望这会对你有所帮助。
Try this,Hope this will help you.
declare @sDate datetime,
@eDate datetime;
select @sDate = '11/4/2014 08:00:00' ,
@eDate = '11/4/2014 20:00:00';
--select @sDate StartDate,@eDate EndDate
;with cte as
(
select @sDate StartDate
,datepart(hour, @sDate) StartTime
, datepart(hour, dateadd(Hour, 1, @sDate)) as EndTime
,convert(varchar(8),datepart(hour, @sDate))
+ ' - '
+ convert(varchar(8),datepart(hour, dateadd(Hour, 1, @sDate))) as Hours
union all
select dateadd(Hour, 1, StartDate)
,datepart(hour, dateadd(Hour, 1, StartDate)) StartTime
, datepart(hour, dateadd(Hour, 2, StartDate)) as EndTime
,convert(varchar(8),datepart(hour, dateadd(Hour, 1, StartDate)))
+ ' - '
+ convert(varchar(8),datepart(hour, dateadd(Hour, 2, StartDate))) as Hours
FROM cte
WHERE dateadd(Hour, 1, StartDate)<= @eDate
)
select * from cte
option (maxrecursion 0)
这篇关于项目查询每小时显示班次的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!