时间戳

首页 > 解决方案 > 连续显示 AM IN、AM OUT、PM IN、PM OUT

sql - 连续显示 AM IN、AM OUT、PM IN、PM OUT

问题描述

我需要从日志表中返回一个移位时钟样式的输入/输出板。

预期的输出是：

注册号。 天 AM_IN AM_OUT PM_IN PM_OUT
1 1 8:36 12:06 1:06 6:36
4 1 7:36 12:06 1:09 7:36

在哪里：

总体而言，结果应按 和ENROLLNO排序DAY

到目前为止，我已经尝试过这个查询，但它没有返回我需要的值：

ALTER PROCEDURE [dbo].[spViewLOGCSF]
    @month nvarchar (max),
    @year nvarchar (max)
AS
    SELECT 
        t1.EnrollNo, t1.Day, 
        MIN(CONVERT(varchar(15), HoursAndMinutes, 100)) AM_IN,
        (SELECT MIN(CONVERT(varchar(15), HoursAndMinutes, 100))
         FROM tblGLog 
         WHERE Month = 'April' 
           AND HoursAndMinutes BETWEEN '11:30' AND '12:30') AM_OUT,
        (SELECT MIN(CONVERT(varchar(15), HoursAndMinutes, 100))
         FROM tblGLog 
         WHERE Month = 'April' 
           AND HoursAndMinutes BETWEEN '12:31' AND '14:00') PM_IN,
        MAX(CONVERT(varchar(15), HoursAndMinutes, 100)) PM_OUT 
    FROM
        tblGLog AS t1
    LEFT JOIN 
        tblEnroll AS t2 ON t1.EnrollNo = t2.EnrollNumber
    WHERE
        t1.Month = @month AND t1.Year = @year
    GROUP BY 
        t1.EnrollNo, t1.Day

源数据表：

表名：TBLLOGS

列是：ENROLLNO、HOURS、DAYS、MONTHS、YEARS、DATETIME

注册号。 小时 天 几个月 年 约会时间
1 8:36 1 四月 2021 2021 年 4 月 1 日上午 8 点 36 分 48 秒
1 12:06 1 四月 2021 2021 年 4 月 1 日下午 12:06:48
1 1:06 1 四月 2021 2021 年 4 月 1 日下午 1:06:48
1 5:36 1 四月 2021 2021 年 4 月 1 日下午 5:36:48
1 6:36 1 四月 2021 2021 年 4 月 1 日下午 6:36:48
4 7:36 1 四月 2021 2021 年 4 月 1 日上午 7 点 36 分 48 秒
4 12:06 1 四月 2021 2021 年 4 月 1 日下午 12:06:48
4 1:09 1 四月 2021 2021 年 4 月 1 日下午 1:09:48
4 5:32 1 四月 2021 2021 年 4 月 1 日下午 5:36:48
4 7:36 1 四月 2021 2021 年 4 月 1 日下午 7:36:48

标签： sqlsql-server

解决方案

您的第一个问题是您的 SP 所针对的架构与您发布的架构不同，它正在查询表tblGLog和tblEnroll

出于本回复的目的，我们将忽略 SP 脚本中提供的信息，而是根据发布的示例数据进行回复！

您不应该使用 CHAR 文字来存储您的时间组件，我们需要的示例数据集中的唯一列是ENROLLNO和DATETIME，可以很容易地推断出日期。

  • Month并且Year 可以帮助优化某些类型的执行计划的索引，但是您的 SP 没有进行过滤，Day因此您将通过存储DATE用于分组的TIME类型化列和用于过滤的类型化列来获得更多价值。

以下解决方案使用CTE转换数据集，这不是 100% 必要的，但它说明了此类查询所需的具体列。您可以通过将此CTE中的其他列包含到您的TBLLOGS架构中并将值适当地设置为默认值、通过触发器或在您的INSERT命令中来优化您的架构。

CROSS APPLY用于转换查询以应用特别请求的列表达式，但是这些列不是修改原始模式的候选者。

注意：时间边界条件已关闭，以便精确记录11:29并14:01包含在内。

WITH TimePeriodData as
(
  SELECT ENROLLNO, [DATETIME], DAYS, HOURS
       , CAST([DATETIME] AS Date) AS [Date]
       , CAST([DATETIME] AS TIME) AS [TIME]
  FROM TBLLOGS
)
SELECT g.ENROLLNO, g.[DATE], g.[DAYS]
     , MIN(x.AM_IN) AS AM_IN
     , MAX(x.AM_OUT) AS AM_OUT  
     , MIN(x.PM_IN) AS PM_IN
     , MAX(x.PM_OUT) AS PM_OUT  
FROM TimePeriodData g
CROSS APPLY (SELECT CASE WHEN [TIME] < '11:30' THEN [HOURS] END AS AM_IN
                  , CASE WHEN [TIME] BETWEEN '11:30' AND '12:30' THEN [HOURS] END AS AM_OUT
                  , CASE WHEN [TIME] BETWEEN '12:31' AND '14:00' THEN [HOURS] END AS PM_IN
                  , CASE WHEN [TIME] > '14:00' THEN [HOURS] END AS PM_OUT
             ) as x
GROUP BY g.ENROLLNO, g.[Date], g.[DAYS];

看到这个小提琴：http ://sqlfiddle.com/#!18/0073d1/1

您的要求似乎非常具体，将中午休息时间指定为更简单的逻辑12:30，然后我们每行只有一次转换。如果这个值是一个常数，那么它就是包含在数据模式中的候选值。

下一个查询故意不按请求格式化DAYS和列的输出HOURS，而是 SQL 查询返回准确的数据类型，以便您可以轻松地将此查询扩展到其他情况。格式化具体的输出应该在表示层处理

WITH TimePeriodData as
(
  SELECT ENROLLNO, [DATETIME], DAYS, HOURS
       , CAST([DATETIME] AS Date) AS [Date]
       , CAST([DATETIME] AS TIME) AS [TIME]
       , CASE WHEN DATEPART(hour, [DATETIME]) >=14 THEN 1 ELSE 0 END AS [IS_PM] 
  FROM TBLLOGS
)
SELECT g.ENROLLNO, g.[DATE]
     , MIN(x.AM) AS AM_IN
     , MAX(x.AM) AS AM_OUT  
     , MIN(x.PM) AS PM_IN
     , MAX(x.PM) AS PM_OUT  
FROM TimePeriodData g
CROSS APPLY (SELECT CASE WHEN [IS_PM] = 0 THEN [TIME] END AS AM
                  , CASE WHEN [IS_PM] = 1 THEN [TIME] END AS PM
             ) as x
GROUP BY g.ENROLLNO, g.[Date], g.[DAYS];

这将返回：http ://sqlfiddle.com/#!18/0073d1/10

注册号 日期 AM_IN AM_OUT PM_IN PM_OUT
1 2021-04-01 08:36:48 12:06:48 13:06:48 18:36:48
4 2021-04-01 07:36:48 12:06:48 13:09:48 19:36:48

以下小提琴http://sqlfiddle.com/#!18/b09ee3/2演示了如何简化查询：

SELECT g.ENROLLNO, g.[DATE]
     , MIN(x.AM) AS AM_IN
     , MAX(x.AM) AS AM_OUT  
     , MIN(x.PM) AS PM_IN
     , MAX(x.PM) AS PM_OUT  
FROM TBLLOGS g
CROSS APPLY (SELECT CASE WHEN [IS_PM] = 0 THEN [TIME] END AS AM
                  , CASE WHEN [IS_PM] = 1 THEN [TIME] END AS PM
             ) as x
GROUP BY g.ENROLLNO, g.[Date];

这里没有关于如何将其应用于原始 SP 的示例，因为没有提供确切的表模式，没有解释LEFT JOIN该 SP 中未使用的引用，并且硬编码'April'过滤忽略了输入参数......太多了我选择不参与其中的混乱；）

推荐阅读