从班次和休息表计算工作时间 [英] Calculate Work Time from Shift and Break Table
问题描述
我使用的是 SQL Server 2008.我有两个表 Shift 和 Break,其中包含以下数据:轮班将是每周 7 天.
I am using SQL Server 2008. I have two table Shift and Break with following data: The shifts will be 7 days week.
SHIFT TABLE 中的数据
Data in SHIFT TABLE
ID Desc Start_Time End_Time
1 1st 07:20:00 15:20:00
2 2nd 15:20:00 23:20:00
3 3rd 23:20:00 07:20:00
BREAK TABLE 中的数据
Data in BREAK TABLE
ID Desc Start_Time End_Time
1 1st Shift - 1st break 09:10:00 09:25:00
2 1st Shift - Lunch 11:30:00 12:05:00
3 1st Shift - 2nd break 13:30:00 13:45:00
4 2nd Shift - 1st break 17:10:00 17:25:00
5 2nd Shift - Lunch 19:30:00 20:05:00
6 2nd Shift - 2nd break 21:30:00 21:45:00
7 3rd Shift - 1st break 01:10:00 01:25:00
8 3rd Shift - Lunch 03:30:00 04:05:00
9 3rd Shift - 2nd break 05:30:00 05:45:00
输出需要类似于:
Start_Time End Time
07:20:00 09:10:00
09:25:00 11:30:00
12:05:00 13:30:00
13:30:00 15:20:00
....
我是新加入的,任何人都可以帮助我加入.此外,如果您认为表格结构的任何更改会有所帮助,请提出建议.
I am new with joins, Can anyone help me on join. Also if you think any changes in table structure will be helpful please suggest.
推荐答案
正如其他人所说,如果没有假设一些事情,这有点模糊.这个问题有更快、更简单的方法,但我尝试尽可能动态地解决我的问题,以适应模糊的定义.以下是我的假设:
As others have stated this is a bit vague without assuming a few things. There are faster, less complicated, ways to this issue but I tried to do my solution as dynamic as possible to suit the vague definition. Here are my assumptions:
这是 SQL Fiddle:SQL Fiddle 演示
Here is the SQL Fiddle: SQL Fiddle Demo
假设
- 假设 SQL Server 2005+
- 假设班次表日期部分是 1900-01-01
- 假设 Break 表具有正确的 StartTime/EndTime 日期
- 假设数据库中只有 1 个人打卡打卡打卡(否EmployeeID 包含在@BREAK 表中)
- 承担他们整个轮班的工作.在轮班时准确打卡在轮班结束时开始和离开.
表格
DECLARE @SHIFT Table (ID INT IDENTITY(1,1) PRIMARY KEY, StartTime DATETIME, EndTime DATETIME)
INSERT INTO @SHIFT (StartTime, EndTime) VALUES
('07:20:00','15:20:00'),
('15:20:00','23:20:00'),
('23:20:00','07:20:00')
DECLARE @BREAK Table (ID INT IDENTITY(1,1) PRIMARY KEY, StartTime DATETIME, EndTime DATETIME)
INSERT INTO @BREAK (StartTime, EndTime) VALUES
('1/1/2013 09:10:00','1/1/2013 09:25:00'),
('1/1/2013 11:30:00','1/1/2013 12:05:00'),
('1/1/2013 13:30:00','1/1/2013 13:45:00'),
('1/1/2013 17:10:00','1/1/2013 17:25:00'),
('1/1/2013 19:30:00','1/1/2013 20:05:00'),
('1/1/2013 21:30:00','1/1/2013 21:45:00'),
('1/2/2013 01:10:00','1/2/2013 01:25:00'),
('1/2/2013 03:30:00','1/2/2013 04:05:00'),
('1/2/2013 05:30:00','1/2/2013 05:45:00'),
('1/2/2013 09:10:00','1/2/2013 09:25:00'),
('1/2/2013 11:30:00','1/2/2013 12:05:00'),
('1/2/2013 13:30:00','1/2/2013 13:45:00'),
('1/2/2013 17:10:00','1/2/2013 17:25:00'),
('1/2/2013 19:30:00','1/2/2013 20:05:00'),
('1/2/2013 21:30:00','1/2/2013 21:45:00'),
('1/2/2013 01:10:00','1/2/2013 01:25:00'),
('1/2/2013 03:30:00','1/2/2013 04:05:00'),
('1/2/2013 05:30:00','1/2/2013 05:45:00')
解决方案
;WITH
MinMaxDates AS --FINDS THE MINIMUM AND MAXIMUM DATE RANGES NEEDING SHIFTS ASSOCIATED.
(
SELECT
CAST(MIN(B.StartTime) AS DATE) AS MinDate,
CAST(MAX(B.EndTime) AS DATE) AS MaxDate
FROM @BREAK AS B
),
RecursiveDateBuilder AS --RECURSIVELY BUILDS A LIST OF DATES BETWEEN THE MINIMUM AND MAXIMUM RANGES IN BREAKS
(
SELECT MinDate AS ShiftStartDate FROM MinMaxDates
UNION ALL
SELECT DATEADD(dd,1,ShiftStartDate) FROM RecursiveDateBuilder WHERE DATEADD(dd,1,ShiftStartDate) <= (SELECT MaxDate FROM MinMaxDates)
),
ShiftSets AS --CREATE A SHIFT SET FOR EVERY DATE
(
SELECT
ROW_NUMBER() OVER (ORDER BY R.ShiftStartDate ASC, S.ID ASC) AS NewShiftID,
S.ID AS OldShiftID,
DATEADD(dd,DATEDIFF(dd,S.StartTime, R.ShiftStartDate),S.StartTime) AS StartDate,
DATEADD(dd,DATEDIFF(dd,S.EndTime, R.ShiftStartDate),S.EndTime) AS EndDate,
R.ShiftStartDate AS ShiftGroup
FROM
@SHIFT AS S
CROSS JOIN RecursiveDateBuilder AS R
),
Shifts AS --FIXES ANY SHIFTS THAT CROSS MIDNIGHT SETTING THEM TO THE NEXT DAY
(
SELECT
S.NewShiftID AS ShiftID,
S.StartDate,
CASE
WHEN S.EndDate <= Min2.MinStartDate THEN DATEADD(DAY,1,S.EndDate)
ELSE S.EndDate
END AS EndDate
FROM
ShiftSets AS S
CROSS APPLY (SELECT MIN(Mins.StartDate) AS MinStartDate FROM ShiftSets AS Mins WHERE Mins.ShiftGroup = S.ShiftGroup) AS Min2
),
BreaksToShifts AS --ASSOCIATES THE PUNCHES TO THE SHIFTS
(
SELECT
B.StartTime AS ClockIn,
B.EndTime AS ClockOut,
S.ShiftID,
S.StartDate,
S.EndDate
FROM
@BREAK AS B
INNER JOIN Shifts AS S ON (B.StartTime BETWEEN S.StartDate AND S.EndDate AND B.EndTime BETWEEN S.StartDate AND S.EndDate)
),
Punches AS
(
SELECT ROW_NUMBER() OVER (ORDER BY S.TheTime ASC) AS ID, S.TheTime FROM
(
SELECT BS.ShiftID, BS.ClockIn AS TheTime FROM BreaksToShifts AS BS
UNION ALL
SELECT BS.ShiftID, MIN(BS.StartDate) AS TheTime FROM BreaksToShifts AS BS GROUP BY BS.ShiftID
UNION ALL
SELECT BS.ShiftID, BS.ClockOut AS TheTime FROM BreaksToShifts AS BS
UNION ALL
SELECT BS.ShiftID, MAX(BS.EndDate) AS TheTime FROM BreaksToShifts AS BS GROUP BY BS.ShiftID
) AS S
)
SELECT
*
FROM
Punches AS P1
INNER JOIN Punches AS P2 ON (P2.ID = P1.ID + 1)
WHERE
P1.ID % 2 > 0
这篇关于从班次和休息表计算工作时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!