当一天变化时 SQL Server 跟踪计划的班次 [英] SQL Server Tracking Scheduled Shifts when the day varies
问题描述
我什至不确定从哪里开始解决这个问题,我需要从我们的 MS SQL 2012 Db 中查询生产数据,该数据库具有基于记录的班次的日期时间戳,棘手的部分是我们运行4 12 小时轮班,以 2 开、2 关、3 开、2 关的模式进行,即这是 2013 年 1 月的前 3 周:
I'm not even sure where to start in solving this one, I need to query production data from our MS SQL 2012 Db that has a datetime stamp based on the shift it was recorded in, the tricky part is that we run 4 12hr shifts in a pattern of 2 on, 2 off, 3 on, 2 off i.e. here's the first 3wks of Jan 2013:
S M T W T F S
1 2 3 4 5
C/D A/B A/B C/D C/D
6 7 8 9 10 11 12
C/D A/B A/B C/D C/D A/B A/B
13 14 15 16 17 18 19
A/B C/D C/D A/B A/B C/D C/D
A&C 是早班 7:00-19:00 和 B&D 是夜班 19:00-7:00 我对 SQL 还很陌生,还没有做过这样的事情,我知道我可以通过以下方式轻松确定 2013 年 1 月 1 日上午班次的数据:
A&C are morning shifts running 7:00-19:00 and B&D are night running 19:00-7:00 I'm fairly new to SQL and haven't had to do anything like this yet, I know I can easily determine the data for the 1/1/2013 AM shift with:
select *
from Line05
where L05Time BETWEEN '01/01/2013 07:00' AND '01/01/2013 19:00'
例如,但我不确定如何将其与 C Shift 联系起来.
for example but I'm not sure how I can tie that to C Shift.
推荐答案
下面列出了一个轮班表.目前还不清楚您想要做什么,但您应该能够使用此处显示的一些计算来逆向工程确定事件日期/时间的偏移.
The following cranks out a table of shifts. It isn't exactly clear what you want to do, but you should be able to reverse engineer determining the shift from the date/time of an event using some of the calculations shown here.
EDIT:更正 case
以处理 2/2/3/2 模式.
EDIT: Corrected case
to handle to 2/2/3/2 pattern.
; with Samples as (
-- Start at the beginning of 2013.
select Cast( '01-01-2013 00:00' as DateTime ) as Sample
union all
-- Add hours up to the desired end date.
select DateAdd( hour, 1, Sample )
from Samples
where Sample <= '2013-01-30'
),
ExtendedSamples as (
-- Calculate the number of days since the beginning of the first shift on 1/1/2013.
select Sample, DateDiff( hour, '01-01-2013 07:00', Sample ) / 24 as Days
from Samples ),
Shifts as (
-- Calculate the shifts for each day.
select *,
case when ( Days + 1 ) % 9 in ( 0, 1, 4, 5 ) then 'C/D' else 'A/B' end as Shifts
from ExtendedSamples )
select *,
case when DatePart( hour, Sample ) between 7 and 18 then Substring( Shifts, 1, 1 ) else Substring( Shifts, 3, 1 ) end as Shift
from Shifts
option ( maxrecursion 0 )
这篇关于当一天变化时 SQL Server 跟踪计划的班次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!