将空值指向下一个日期的MAX到MIN [英] Point the null value in MAX to MIN of next date

问题描述

你好。





我们有一个考勤DB，我们试图将它们分类为IN和OUT，查询已发布这里

Hello There


We have an attendance DB where we are trying to get them sorted as IN and OUT, the Query posted here

SELECT employee_code,CONVERT(DATE,([attendance_date_with_time]) ) AS [DATE],MIN([attendance_date_with_time]) AS [IN],MAX([attendance_date_with_time]) AS [OUT] FROM attendanceBioMetric GROUP BY Employee_Code,CONVERT(DATE,([attendance_date_with_time]) ) order by DATE desc

为A-shift和Gen的员工正确提供数据-shift，因为他们的登录日期和退出日期是在同一天。而对于B班次员工，OUT数据返回MIN值，因为注销日期与登录日期不同。



有没有人能解决这个问题



谢谢



我尝试了什么：



没什么，

但是，我有有一个建议，我们可以指向Max函数中的NULL来返回下一个日期的MIN值。

需要相同的代码，

Gives us the data correctly for the Employees in A-shift as well as Gen-shift, coz their Log-In date and Logout date falls on the same day. whereas for the B-shift employees, the OUT data is returning the MIN value as the Log-Out date is different from the Login date.

Does anyone have a solution to this problem

Thank you

What I have tried:

Nothing,
But, I have got a suggestion, can we point the NULL in the Max Function to return the MIN value of the Next date.
Need a code for the same,

推荐答案

而不是使用MAX和MIN命令记录员工和日期顺序然后使用LEAD或LAG确定适当的输入/输出日期 - MySQL :: MySQL 8.0参考手册:: 12.20.1窗口功能描述 [ ^ ]

Instead of using MAX and MIN order the records in employee and date order and then use LEAD or LAG to determine the appropriate in/out dates - MySQL :: MySQL 8.0 Reference Manual :: 12.20.1 Window Function Descriptions[^]

这篇关于将空值指向下一个日期的MAX到MIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！