时间戳

首页 > 解决方案 > 如何在同一行中返回每周轮班开始和结束预定时间？

sql - 如何在同一行中返回每周轮班开始和结束预定时间？

问题描述

一名员工每天早上 9 点到下午 5 点工作，连续工作 7 天。有时轮班会发生变化。所以我想在 1 行中返回整周的预定时间，如下所示：

我想要以下内容：

Sat In           Sat Out          Sun In           Sun Out
---------------- ---------------- ---------------- ----------------
09:00:00.0000000 17:00:00.0000000 09:00:00.0000000 17:00:00.0000000

但相反，我得到：

Sat In           Sat Out          Sun In           Sun Out
---------------- ---------------- ---------------- ----------------
NULL             NULL             NULL             NULL
NULL             NULL             09:00:00.0000000 17:00:00.0000000
09:00:00.0000000 10:30:00.0000000 NULL             NULL
10:30:00.0000000 17:00:00.0000000 NULL             NULL

我的部分代码：

 Select DISTINCT
             case DATENAME(dw, Date) when 'Monday' then CAST(Start AS TIME)  end as [Mon In]
            ,case DATENAME(dw, Date) when 'Monday' then CAST(End AS TIME)  end as [Mon Out]
            ,case DATENAME(dw, Date) when 'Tuesday' then CAST(Start AS TIME)  end as [Tue In]
            ,case DATENAME(dw, Date) when 'Tuesday' then CAST(End AS TIME) end as [Tue Out]
            ,case DATENAME(dw, Date) when 'Wednesday' then CAST(Start AS TIME)  end as [Wed In]
            ,case DATENAME(dw, Date) when 'Wednesday' then CAST(End AS TIME) end as [Wed Out]
    from Table1 t
        inner join Table2 s on s.ID = t.ID
        where Date between '2020-03-01' AND '2020-03-07'

标签： sql

解决方案

您可以将查询包装在子查询中，然后汇总如下：

SELECT min([Mon In]), max([Mon Out]), min([Tue In])....
FROM (
        Select DISTINCT
             case DATENAME(dw, Date) when 'Monday' then CAST(Start AS TIME)  end as [Mon In]
            ,case DATENAME(dw, Date) when 'Monday' then CAST(End AS TIME)  end as [Mon Out]
            ,case DATENAME(dw, Date) when 'Tuesday' then CAST(Start AS TIME)  end as [Tue In]
            ,case DATENAME(dw, Date) when 'Tuesday' then CAST(End AS TIME) end as [Tue Out]
            ,case DATENAME(dw, Date) when 'Wednesday' then CAST(Start AS TIME)  end as [Wed In]
            ,case DATENAME(dw, Date) when 'Wednesday' then CAST(End AS TIME) end as [Wed Out]
        from Table1 t
          inner join Table2 s on s.ID = t.ID
        where Date between '2020-03-01' AND '2020-03-07'
    ) subquery

话虽如此，如果我们有样本数据（来自您的Table1and Table2），我们可能能够帮助使用交叉表或数据透视表（取决于这是 Access 还是 Sql Server）获得更高性能的查询

推荐阅读