sql - 无法从每个后天的日期时间列中获取 MIN 和 MAX 时间

Question

我正在尝试从 Datetime (InOut) 列获取 MIN 和 MAX 时间，但输出是两列相同。

我的查询:

SELECT
  CONVERT(NVARCHAR(12),MIN(Punch_history.Ecode)) as EmpCode,
  CONVERT(NVARCHAR(12),MIN(EmployeeMaster.RecommandedBy)) as EmpID,
  convert(date, InOut) as Report_date,
  CONVERT(VARCHAR(10),(InOut),108) as InTime,
  CONVERT(VARCHAR(10),(InOut),108) as OutTime,
  CONVERT(NVARCHAR(12),MIN(TID)) as LOCATION
  FROM Punch_history inner join EmployeeMaster on Punch_history.ECode = EmployeeMaster.Ecode
  where CAST (InOut as DATE) between  CAST(getdate() -1 as DATE ) and CAST(getdate() -1 as DATE ) and 
  EmployeeMaster.RecommandedBy like 'M0%' group by EmpID,InOut

Answer 1

尝试按转换后的日期而不是 inout 进行分组。

SELECT
  CONVERT(NVARCHAR(12),MIN(Punch_history.Ecode)) as EmpCode,
  convert(date, InOut) as Report_date,
  CONVERT(NVARCHAR(10),min(InOut),108) as InTime,
  CONVERT(NVARCHAR(10),max(InOut),108) as OutTime
  FROM Punch_history 
  where CAST (InOut as DATE) between  CAST(getdate() -1 as DATE ) 
    and CAST(getdate() -1 as DATE ) 
  group by EmpID, convert(date, InOut)