sql - 无法从每个 backday 的 Datetime 列中获取 MIN 和 MAX 时间

Question

我正在尝试从( ) 列获取时间，MIN但两列的输出相同。MAXDatetimeInOut

我的查询：

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

标签： sqlsql-serversql-server-2008

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)