在重复的表sql server中获取最大日期和时间

i want to ask about how to get sort of maximum date and maximum time when there is a lot of duplicate data.

我想问一下，当有大量重复数据时，如何获得最大日期和最长时间。

i am using this query to show the data

我正在使用此查询来显示数据

/****** Script for SelectTopNRows command from SSMS ******/

/ ******来自SSMS的SelectTopNRows命令的脚本****** /

SELECT  
      trx.[ActivityDate]
      ,trx.ActivityTimeStart
      ,trx.ActivityTimeEnd
      ,trx.[EmployeeID]
      ,trx.[ApprovalStatusID]
      ,mst.[FullName]
  FROM [KairosManagementSystem].[dbo].[Tbl_Trx_TimeSheet] trx
  INNER JOIN [dbo].[Tbl_Mst_Employee] mst
  ON trx.[EmployeeID] = mst.[EmployeeID]
  where datepart(year,trx.[ActivityDate]) between datepart(year, dateadd(year,-1,getdate())) and datepart(year, getdate())
  and
  trx.[ActivityDate] <= getdate()
  and
  trx.EmployeeID = 11460 
order by trx.[ActivityDate] DESC

as you can see, the result from this is like this image below.

如您所见，此结果如下图所示。

the question is how to get the result like this. where i want to get the minimum ActivitityTimeStart and maximum ActivityTimeEnd in respective date

问题是如何得到这样的结果。我希望在相应的日期获得最小的ActivitityTimeStart和最大的ActivityTimeEnd

 ----------------------------------------------------------------------------
 |ActivityDate | ActivityTimeStart | ActivityTimeEnd  | EmployeeID | FullName
 ----------------------------------------------------------------------------
 |2017-02-17   | 07:00:00 00:00:00 | 16:00:00 00:00:00| 11460      | Yohanes 

1 个解决方案

#1

Do you just want a simple GROUP BY?

你想要一个简单的GROUP BY吗？

SELECT trx.[ActivityDate], MIN(trx.ActivityTimeStart), MAX(trx.ActivityTimeEnd),
       trx.[EmployeeID], mst.[FullName]
FROM [KairosManagementSystem].[dbo].[Tbl_Trx_TimeSheet] trx INNER JOIN
     [dbo].[Tbl_Mst_Employee] mst
     ON trx.[EmployeeID] = mst.[EmployeeID]
WHERE YEAR(trx.[ActivityDate]) between YEAR(dateadd(year,-1,getdate())) and YEAR(getdate()) and
      trx.[ActivityDate] <= getdate() and
      trx.EmployeeID = 11460 
GROUP BY trx.[ActivityDate], trx.[EmployeeID], mst.[FullName]
ORDER BY trx.[ActivityDate] DESC;