如何获得每个日期的最小时间值和第二个最小值？

问题描述：

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

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
;

如何获得每个日期的最小时间值和第二个最小值？

相关推荐