跨越午夜的时间记录拆分

Question

我是一名有用的助手，可以为您翻译文本。

我正在尝试运行一些报告，并处理员工劳动时间跨越午夜的整个问题。然而，我想到了一个解决方案，即将跨越午夜的记录拆分为两条记录，就好像员工在午夜打卡下班，同时在午夜重新打卡上班，从而完全避免午夜问题。

因此，如果我有：

EmployeeId   InTime                     OutTime
---          -----------------------    -----------------------
1            2012-01-18 19:50:04.437    2012-01-19 03:30:02.433

你认为最优雅的方法是如何拆分这条记录：

EmployeeId   InTime                     OutTime
---          -----------------------    -----------------------
1            2012-01-18 19:50:04.437    2012-01-19 00:00:00.000
1            2012-01-19 00:00:00.000    2012-01-19 03:30:02.433

是的，我已经仔细考虑了这对现有功能可能产生的影响……这就是为什么我选择在一个临时表中进行操作，以免影响现有功能。

- Brandon Moore

---

请查看http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516&bcsi_scan_0070CC03028EB70D=JJykjVHNGapjNgIKjEMdLQkAAADDDLIH&bcsi_scan_filename=topic.asp，虽然不完全相似，但它是基于SQL 2k的解决方案。我希望它能给你一些指引。 - vmvadivel

所以现在跨越了午夜……问题是什么呢？给定班次中总工作小时数？还是如果跨越了两个日期，应该显示哪个日期？ - DRapp

@DRapp 为了本报告的目的（虽然不是全部），劳动力应该出现在它实际属于的日期上...而不一定是员工开始轮班的日期。因此，我认为这是最好的解决方案，以便数据在处理时具有最合理的意义。 - Brandon Moore

这里有任何需要关注的舍入问题吗？例如，如果你要将工作时间四舍五入到最接近的半小时，但是他们在23:40打卡，在00:20打卡，那么你会将两个工作时间都舍入为零，但实际上应该计入半小时。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 不，它永远不会四舍五入。 - Brandon Moore

Answer 1

这可能会有所帮助：

DECLARE @tbl TABLE 
    (
        EmployeeId INT,
        InTime DATETIME,
        OutTime DATETIME
    )

INSERT INTO @tbl(EmployeeId,InTime,OutTime) VALUES (1,'2012-01-18 19:50:04.437','2012-01-19 03:30:02.433')
INSERT INTO @tbl(EmployeeId,InTime,OutTime) VALUES (2,'2012-01-18 19:50:04.437','2012-01-18 20:30:02.433')
INSERT INTO @tbl(EmployeeID,InTime,OutTime) VALUES (3,'2012-01-18 16:15:00.000','2012-01-19 00:00:00.000')
INSERT INTO @tbl(EmployeeID,InTime,OutTime) VALUES (4,'2012-01-18 00:00:00.000','2012-01-18 08:15:00.000')
SELECT
    tbl.EmployeeId,
    tbl.InTime,
    DATEADD(dd, DATEDIFF(dd, 0, tbl.OutTime), 0) AS OutTime
FROM
    @tbl AS tbl
WHERE
    DATEDIFF(dd,tbl.InTime,tbl.OutTime)=1
UNION ALL
SELECT
    tbl.EmployeeId,
    CASE WHEN DATEDIFF(dd,tbl.InTime,tbl.OutTime)=1
        THEN DATEADD(dd, DATEDIFF(dd, 0, tbl.OutTime), 0)
        ELSE tbl.InTime
    END AS InTime,
    tbl.OutTime
FROM @tbl AS tbl
ORDER BY EmployeeId

- Arion

Answer 2

下面的解决方案使用一个数字表（形式为 master..spt_values 系统表的子集）来拆分时间范围。它可以拆分跨越任意天数的范围（使用 spt_values 可以拆分最多 2048 天，但您可以使用自己的数字表设置不同的最大值）。此处未涉及跨度为 1 天和 2 天的具体情况，但我认为这种方法足够轻量级，适合您尝试：

;
WITH LaborHours (EmployeeId, InTime, OutTime) AS (
  SELECT
    1,
    CAST('2012-01-18 19:50:04.437' AS datetime),
    CAST('2012-01-18 03:30:02.433' AS datetime)
),
HoursSplit AS (
  SELECT
    h.*,
    SubInTime  = DATEADD(DAY, DATEDIFF(DAY, 0, h.InTime) + v.number + 0, 0),
    SubOutTime = DATEADD(DAY, DATEDIFF(DAY, 0, h.InTime) + v.number + 1, 0)
  FROM LaborHours h
    INNER JOIN master..spt_values v
      ON number BETWEEN 0 AND DATEDIFF(DAY, h.InTime, h.OutTime)
  WHERE v.type = 'P'
),
HoursSubstituted AS (
  SELECT
    EmployeeId,
    InTime  = CASE WHEN InTime  > SubInTime  THEN InTime  ELSE SubInTime  END,
    OutTime = CASE WHEN OutTime < SubOutTime THEN OutTime ELSE SubOutTime END
  FROM HoursSplit
)
SELECT *
FROM HoursSubstituted

基本上，这是一个两步法。

首先，我们使用数字表格将每一行复制多次，次数为范围所跨越的天数，并准备从午夜开始到下一个午夜结束的“标准”子范围。

接下来，我们比较子范围的开始与范围的开始，以确定它是否为第一个子范围，在这种情况下，我们使用 InTime 作为其开始。类似地，我们比较结束时间，以确定我们应该使用 OutTime 还是仅使用午夜作为该子范围的结束。

- Andriy M

Answer 3

在接受的答案基础上，作为一个MySQL新手，我扩展了代码以更好地理解每种情况。

SELECT
    tbl.EmployeeId,
    Datediff(tbl.OutTime, tbl.InTime) as DD,
    -- Change outTime to end of the day if shift is overnight
    DATE_FORMAT(tbl.InTime, "%Y-%m-%d %H:%i:%s") as InTime,
    CASE WHEN Datediff(tbl.OutTime, tbl.InTime) = 1
        THEN DATE_FORMAT(tbl.OutTime, "%Y-%m-%d 00:00:00")
        ELSE DATE_FORMAT(tbl.OutTime, "%Y-%m-%d %H:%i:%s")
    END AS OutTime    
FROM tbl 
UNION DISTINCT
SELECT
    tbl.EmployeeId,
    Datediff(tbl.OutTime,tbl.InTime) as DD,
    -- Change inTime to beginning of the next day if shift is overnight
    CASE WHEN Datediff(tbl.OutTime,tbl.InTime) = 1
        THEN DATE_FORMAT(tbl.OutTime, "%Y-%m-%d 00:00:00")
        ELSE DATE_FORMAT(tbl.InTime, "%Y-%m-%d %H:%i:%s")
    END AS InTime,
    DATE_FORMAT(tbl.OutTime, "%Y-%m-%d %H:%i:%s") as OutTime
FROM tbl
order by EmployeeId

- topal

Answer 4

如果是为了报告，那么您应该只需要查询/联合，从原始记录中提取符合这些条件的两个记录... 没有 SQL-Server 2008，我只能为您提供一个伪代码查询。

第一部分基于您的范围条件获取所有记录。 "OutTime" 的值是有条件的... 如果它在同一天，那么没有交叉，只需使用 out 时间。如果它在下一天，使用转换来动态构建一个 'YYYY-MM-DD' 日期（默认为 00:00:00 时间），作为 OUT 时间。

UNION 只会抓取那些在第一个 WHERE 中符合资格的相同记录，其中 in/out 日期不同。因此，我们知道我们希望无论 OutTime 是什么都充当 InTime，但基于 "00:00:00" 时间，因此执行完全相同的日期/时间字段转换，并且对于这些记录，只需使用最终的 "OutTime" 值即可。

额外的“TimeSplit”列的值为“1”或“2”，旨在确保我们仍然可以按员工ID进行分组，但从中确保“1”条目（开始班次）排在首位，其后是同一人的任何相应的具有“2”条目的班次重叠日。

select
      tc.EmployeeID,
      '1' as TimeSplit,
      tc.InTime,
      case when datepart( dd, tc.InTime ) = datepart( dd, tc.OutTime )
         then tc.OutTime 
         else CAST( CAST( datepart(yyyy, tc.OutTime ) AS varchar) 
              +'-'+ CAST( datepart( mm, tc.OutTime ) AS varchar) 
              +'-'+ CAST( datepart( dd, tc.OutTime ) AS varchar) AS DATETIME)
         end as OutTime
   from 
      TimeCard tc
   where
      YourDateRangeConditions...
   ORDER BY
      tc.EmployeeID, 
      TimeSplit
UNION ALL
select
      tc.EmployeeID,
      '2' as TimeSplit,
      CAST(    CAST( datepart(yyyy, tc.OutTime ) AS varchar) 
         +'-'+ CAST( datepart( mm, tc.OutTime ) AS varchar) 
         +'-'+ CAST( datepart( dd, tc.OutTime ) AS varchar) AS DATETIME)
         end as InTime
      tc.OutTime
   from 
      TimeCard tc
   where
      YourDateRangeConditions...
      AND NOT datepart( dd, tc.InTime ) = datepart( dd, tc.OutTime )

- DRapp

Answer 5

尝试这个，因为你可以通过选择进行插入，在你的选择中，你可以设置要使用的值为不同的日期。

对于添加新行：

insert into table ("EMPLOYEE_ID","INTIME","OUTTIME") values
SELECT EMPLOYEE_ID,date(INTIME),OUTTIME
FROM table
where date(intime) < date(outtime)

更新原始行：

update table 
set outtime =date(outtime) 
where date(intime)= date(outtime)

- em3ricasforsale