如何获得每个日期的最小时间值和第二个最小值？ [英] How can get the the smallest Time value and the second minimum value for each date ?

问题描述

我正在尝试显示每个日期的最小时间值和第二个最小时间值，以及最大日期值和第二个最大时间值：

I'm trying to display the smallest time value and the second minimum time value, and the biggest date value and the second maximum time value for each date:

select  max (times_out), min (times_out), (SELECT MAX( times_out )
  FROM Emp_Attendance
 WHERE times_out < ( SELECT MAX( times_out )
                 FROM Emp_Attendance ) ),

				 (SELECT Min( times_out )
  FROM Emp_Attendance
 WHERE times_out > ( SELECT Min( times_out )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35 )), 

				 min (Times_IN), max(Times_IN),
				 (SELECT Min( Times_IN )
  FROM Emp_Attendance
 WHERE Times_IN > ( SELECT Min( Times_IN )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35)),
				  (SELECT Max( Times_IN )
  FROM Emp_Attendance
 WHERE Times_IN < ( SELECT Max( Times_IN )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35)),
				 CAST(_Date AS DATE) as DateAttendance





				 from Emp_Attendance  where month (Times_Out) =6 and EmpID=35
				 group by CAST(_Date AS DATE) 

的最小值和最大值显示正确的值，但对于其他值，它显示所有日期的相同值。

任何解决方案请

for the min and max it display a right values but for the others it displays the same values for all the dates.
Any solution please§

推荐答案

目前还不完全清楚你想要获得什么输出。这会有什么帮助？

It's not entirely clear what output you're trying to get. Would something like this help?

WITH cte As
(
    SELECT
        EmpID,
        CAST(_Date As date) As DateAttendance,
        Times_In,
        Times_Out,
        ROW_NUMBER() OVER (PARTITION BY EmpID, CAST(_Date As date) ORDER BY Times_In) As RNI,
        ROW_NUMBER() OVER (PARTITION BY EmpID, CAST(_Date As date) ORDER BY Times_Out DESC) As RNO
    FROM
        dbo.Emp_Attendance
    WHERE
        Month(Times_Out) = 6
    And
        EmpID = 35
)
SELECT
    EmpID,
    DateAttendance,
    Max(CASE WHEN RNI = 1 THEN Times_In ELSE Null END) As FirstTimeIn,
    Max(CASE WHEN RNI = 2 THEN Times_In ELSE Null END) As SecondTimeIn,
    Max(CASE WHEN RNO = 2 THEN Times_Out ELSE Null END) As SecondLastTimeOut,
    Max(CASE WHEN RNO = 1 THEN Times_Out ELSE Null END) As LastTimeOut
FROM
    cteRawData
GROUP BY
    EmpID,
    DateAttendance
;

这篇关于如何获得每个日期的最小时间值和第二个最小值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！