如何在SQL Server的同一列中获取两个日期的差异

Question

如何获取同一列中两个相同日期的时差？如果同一天有多个进出怎么办？

表 tbl_Employee_MasterList ：

id  First_Name       Last_Name    biometric_no
----------------------------------------------
125 ABRAHAM JOSEPH   MOQUETE           78

表officer_timelogs

employee_id      record_time             Day      type
--------------------------------------------------------
125         2018-02-27 18:03:31.000    Tuesday     1
125         2018-02-27 07:54:03.000    Tuesday     0

SQL查询：

select 
    a.employee_id, a.record_time, 
    --CONVERT(char(10), a.record_time, 103) as [Date],
    DATENAME(WEEKDAY, a.record_time) as [Day],
    a.[type],
    case 
       when a.[type] = 0
          then 'in'
       when a.[type] = 1
          then 'out'
    end as Status
from
    officer_timelogs a
left join 
    tbl_Employee_MasterList b on a.employee_id = b.biometric_no
where 
    a.employee_id = '125'
order by 
    a.record_time desc

输出：

employee_id record_time               Day       type    Status
---------------------------------------------------------------
     125    2018-02-28 07:47:23.000 Wednesday   0        in
     125    2018-02-27 18:03:31.000 Tuesday     1        out
     125    2018-02-27 07:54:03.000 Tuesday     0        in
     125    2018-02-26 18:01:59.000 Monday      1        out
     125    2018-02-26 07:48:25.000 Monday      0        in
     125    2018-02-24 12:50:00.000 Saturday    1        out
     125    2018-02-24 07:44:16.000 Saturday    0        in
     125    2018-02-23 17:02:06.000 Friday      1        out
     125    2018-02-23 07:48:26.000 Friday      0        in
     125    2018-02-22 18:02:35.000 Thursday    1        out
     125    2018-02-22 07:48:41.000 Thursday    0        in

所需的输出(我想像这样检索数据(：

employee_id    Date         Day       Hours
-----------------------------------------------------
   125         2018-02-27   Tuesday   10:09:28 (h:m:s) 

我正在使用 SQL Server 2012。

Answer 1

我只用了一个表格来简化。我加入并获得雇员的名字并不难。此查询按照您的要求计算先进和后出之间的时差。如果缺少 in 或 out，则返回 '00：00：00'

declare @t table (
    employee_id int
    , record_time datetime
    , type int
)
insert into @t 
values (125, '20180228 07:47:23.000', 0)
    , (125, '20180227 18:03:31.000', 1), (125, '20180227 07:54:03.000', 0)
    , (125, '20180226 18:01:59.000', 1), (125, '20180226 07:48:25.000', 0)
    , (125, '20180224 12:50:00.000', 1), (125, '20180224 07:44:16.000', 0)
    , (125, '20180223 17:02:06.000', 1), (125, '20180223 07:48:26.000', 0)
    , (125, '20180222 18:02:35.000', 1), (125, '20180222 07:48:41.000', 0)
select
    employee_id, [date]
    , [hours] = right(concat('00', diff / 3600), 2) + ':' + right(concat('00', diff % 3600 / 60), 2) + ':' + right(concat('00', diff % 60), 2)
from (
    select
        employee_id, [date] = cast(record_time as date)
        , diff = datediff(ss, min(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
    from
        @t
    group by employee_id, cast(record_time as date)
) t

但我建议看看这个查询。它分别计算每个进出时间，然后获得每天的总和

select
    employee_id, [date]
    , [hours] = right(concat('00', diff / 3600), 2) + ':' + right(concat('00', diff % 3600 / 60), 2) + ':' + right(concat('00', diff % 60), 2)
from (
    select
        employee_id, [date] = cast(isnull([in], [out]) as date)
        , diff = sum(diff)
    from (
        select
            employee_id, [in] = max(iif(type = 0, record_time, null))
            , [out] = max(iif(type = 1, record_time, null))
            , diff = datediff(ss, max(iif(type = 0, record_time, null)), max(iif(type = 1, record_time, null)))
        from (
            select
                *, grp = sum(iif(type = 0, 1, 0)) over (partition by employee_id order by record_time)
            from
                @t
        ) t
        group by employee_id, grp
    ) t
    group by employee_id, cast(isnull([in], [out]) as date)
) t