时间戳

首页 > 解决方案 > SQL 更好的方法来查找两次之间的小时数

sql - SQL 更好的方法来查找两次之间的小时数

问题描述

我正在使用 SQL Server，并且我有一个包含班次的表，他们的开始/结束日期时间，我想找到这些班次的所有时间，这些时间在上午 9 点到下午 3 点之间。班次不得超过 24 小时（目前）。我已经编写了查询，它可以工作，但它很长。考虑到可以在不同日期开始或结束的轮班，它会变得更长。有没有更短、更有效的方法可以写出来？我写它是为了忽略 24 的限制，但仍然只考虑班次只会重叠 2 天的情况。

Select apc.in_punch_time, apc.out_punch_time,

case 
    when FORMAT(apc.in_punch_time,'HH:mm') <= '09:00' and FORMAT(apc.out_punch_time,'HH:mm') between '09:00' and '15:00' and DATEPART(day,apc.in_punch_time) = DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute, DATETIMEFROMPARTS(YEAR(apc.out_punch_time),MONTH(apc.out_punch_time),day(apc.out_punch_time),9,0,0,0), apc.out_punch_time)
    when FORMAT(apc.in_punch_time,'HH:mm') >= '09:00' and FORMAT(apc.out_punch_time,'HH:mm') <= '15:00' and DATEPART(day,apc.in_punch_time) = DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute,apc.in_punch_time,apc.out_punch_time)
    when FORMAT(apc.in_punch_time,'HH:mm') between '09:00' and '15:00' and FORMAT(apc.out_punch_time,'HH:mm') >= '15:00' and DATEPART(day,apc.in_punch_time) = DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute,apc.in_punch_time, DATETIMEFROMPARTS(YEAR(apc.in_punch_time),MONTH(apc.in_punch_time),day(apc.in_punch_time),15,0,0,0)) 
    when FORMAT(apc.in_punch_time,'HH:mm') <= '09:00' and FORMAT(apc.out_punch_time,'HH:mm') >= '15:00' and DATEPART(day,apc.in_punch_time) = DATEPART(day,apc.out_punch_time) then
        360
    when FORMAT(apc.in_punch_time,'HH:mm') < '09:00'  and FORMAT(apc.out_punch_time,'HH:mm') <= '09:00' and DATEPART(day,apc.in_punch_time) <> DATEPART(day,apc.out_punch_time) then
        360 
    when FORMAT(apc.in_punch_time,'HH:mm') between '9:00' and '15:00' and FORMAT(apc.out_punch_time,'HH:mm') <= '09:00' and DATEPART(day,apc.in_punch_time) <> DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute, apc.in_punch_time, DATETIMEFROMPARTS(YEAR(apc.in_punch_time),MONTH(apc.in_punch_time),day(apc.in_punch_time),15,0,0,0))
    when FORMAT(apc.in_punch_time,'HH:mm') <= '15:00' and FORMAT(apc.out_punch_time,'HH:mm') > '09:00' and DATEPART(day,apc.in_punch_time) <> DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute, apc.in_punch_time, DATETIMEFROMPARTS(YEAR(apc.in_punch_time),MONTH(apc.in_punch_time),day(apc.in_punch_time),15,0,0,0)) + 
        DATEDIFF(minute, DATETIMEFROMPARTS(YEAR(apc.out_punch_time),MONTH(apc.out_punch_time),day(apc.out_punch_time),9,0,0,0), apc.out_punch_time)
    when FORMAT(apc.in_punch_time,'HH:mm') >= '15:00' and FORMAT(apc.out_punch_time,'HH:mm') between '09:00' and '15:00' and DATEPART(day,apc.in_punch_time) <> DATEPART(day,apc.out_punch_time) then
        DATEDIFF(minute, DATETIMEFROMPARTS(YEAR(apc.out_punch_time),MONTH(apc.out_punch_time),day(apc.out_punch_time),9,0,0,0), apc.out_punch_time)
    when FORMAT(apc.in_punch_time,'HH:mm') = '00:00' and FORMAT(apc.out_punch_time,'HH:mm') = '00:00' and DATEPART(day,apc.in_punch_time) <> DATEPART(day,apc.out_punch_time) then
        360
    ELSE 0
END hours_between_9_3

from table apc

我的案例是：

同一天

  1. 班次在 9 点之前/9 点开始，在 9 点和 3 点之间结束
  2. 班次在 9 点/之后开始，在 3 点之前结束
  3. 班次从 9 点到 3 点开始，在 3 点/之后结束
  4. 班次在 9 点之前/9 点开始并在 3 点之后结束

不同的日子

  1. 轮班在第 1 天的 9 点之前开始，并在第 2 天的 9 点之前/9 点结束
  2. 班次从第 1 天的 9 点到 3 点开始，在第 2 天的 9 点/之前结束
  3. 班次从第 1 天的 9 点到 3 点开始，在第 2 天的 9 点之后结束
  4. 班次在第 1 天的 3 点/之后结束，在第 2 天的 9 点和 3 点之间结束
  5. 轮班从第 1 天的午夜开始，到第 2 天的午夜结束

感谢您的任何帮助。

标签： sqlsql-server

解决方案

这是我的查询。它适用于任意天数。我要添加三个部分：

  • 第一天 09:00 至 15:00 工作时间
  • 最后一天 09:00 到 15:00 之间的工作时间（如果是第一天之后的另一天）
  • 09:00 到 15:00 之间的工作时间，即 6 小时 = 360 分钟，在它们之间的任何日子

整件事读起来有点乏味（但是，写起来也很乏味）。

with data as
(
  select
    in_punch_time,
    out_punch_time,
    cast (in_punch_time as date) as start_date,
    cast (out_punch_time as date) as end_date,
    cast (in_punch_time as time) as start_time,
    cast (out_punch_time as time) as end_time
  from apc
)
select 
  in_punch_time,
  out_punch_time,
  -- first day
  datediff
  (
    minute,
    case when start_time < '09:00' then
      '09:00'
    when start_time > '15:00' then
      '15:00'
    else
      start_time
    end,
    case when start_date < end_date then
      '15:00'
    when end_time > '15:00' then
      '15:00'
    when end_time < '09:00' then
      '09:00'
    else
      end_time
    end
  ) +
  -- last day (if different from first day)
  case when end_date > start_date and end_time > '09:00' then
    datediff
    (
      minute,
      '09:00',
      case when end_time > '15:00' then
        '15:00'
      when end_time < '09:00' then
        '09:00'
      else
        end_time
      end 
    )
  else
    0
  end +
  -- between days (if any between first and last day)
  case when datediff(day, start_date, end_date) > 1 then
    (datediff(day, start_date, end_date) - 1) * 360 -- minutes
  else
    0
  end as minutes_from_0900_to_1500
from data;

演示：https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2318dc76adf43cd75e52b378b20b7cbc

推荐阅读