Point the null value in MAX to MIN of Next Date
-
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 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
-
The problem is that you are grouping on the DATE instead of the shift. Without sample data, it's hard to tell you how to go about grouping by the shift instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Drew
We have not configured any shift timing, we are simply assuming that the MIN value to be the Login Time and Max Value to be the Log out time,(for A-shift and Gen-shift) and we cross-check the Login Hrs by taking the time difference between the MIN and MAX(which is not present in the Code and done through excel), where even if the employee misses the Punch whether he is in A-shift and Gen Shift or b-shift, we can easily find it out, and cut off time for an employee can be around 24hrs and We have 7 biometric machines, the machines 1,2,3,4 are used for marking the IN punch and the machines 5,6,7 are used to mark the out punch, and the BiometricMachine_ID is the table where this info is stored kindly refer the pic below for the table schema, the typical shift timing for a shift starts at 7:30 am, G-shift starts at 9:00 am and b-shift starts at 6:00 pm
- it.blr - Tuesday, September 4, 2018 11:24 PMDrew
We have not configured any shift timing, we are simply assuming that the MIN value to be the Login Time and Max Value to be the Log out time,(for A-shift and Gen-shift) and we cross-check the Login Hrs by taking the time difference between the MIN and MAX(which is not present in the Code and done through excel), where even if the employee misses the Punch whether he is in A-shift and Gen Shift or b-shift, we can easily find it out, and cut off time for an employee can be around 24hrs and We have 7 biometric machines, the machines 1,2,3,4 are used for marking the IN punch and the machines 5,6,7 are used to mark the out punch, and the BiometricMachine_ID is the table where this info is stored kindly refer the pic below for the table schema, the typical shift timing for a shift starts at 7:30 am, G-shift starts at 9:00 am and b-shift starts at 6:00 pm
Your assumption is wrong for B shift. You're grouping by day, but B shift crosses days, so you're splitting each B shift into two separate groups. Furthermore, each group (day) contains parts of two different B shifts. So, for B shift, your MIN is coming from the last part of one shift and your MAX is coming from the first part of the next shift. You will continue to have this problem as long as you are using groups that split your B shift.
You need to come up with some way to ensure that each of your shifts is in the same group, which is why I recommended grouping by shift. Since your biometric data doesn't contain that information, you'll need to come up with some other way to determine the correct groupings for that data. Since you posted a picture (which is worthless) I'm unable to help you with that. Try posting actual data, and then someone might be able to help.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply