CODEWITHSUNDEEP
sql-server
IT Department
IT Department

Reputation: 13

Point the null value in MAX to MIN of Next Date

[Table screen shot here][2]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, because their Log-In date and Logout date falls on the same day.

Whereas for the B-shift employees,whose logout time falls on next date, 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

Refer Pic for your reference

AttendanceBioMetric_ID BiometricMachine_ID Employee_Code Attendance_Date_With_Time Created_Date Status
1 1 NULL 16-07-18 5:53 PM NULL 1 2 1 NULL 16-07-18 5:53 PM NULL 1 3 1 NEB/0134 26-07-18 12:44 PM NULL 1 4 1 NEB/0134 26-07-18 12:44 PM NULL 1 5 1 NEB/0134 26-07-18 12:44 PM NULL 1 6 1 NEB/0134 26-07-18 12:44 PM NULL 1 7 1 NEB/0134 26-07-18 12:47 PM NULL 1 8 1 NEB0141 26-07-18 12:47 PM NULL 1 9 1 NEB0141 26-07-18 12:49 PM NULL 1 10 1 NEB0141 26-07-18 12:50 PM NULL 1 11 1 NEB0141 26-07-18 12:50 PM NULL 1 12 1 NEB0141 26-07-18 12:50 PM NULL 1 13 1 NEB0141 26-07-18 12:50 PM NULL 1 14 6 NEB/0134 28-07-18 10:05 AM NULL 1 15 6 NEB/0134 28-07-18 10:05 AM NULL 1 16 6 SH0064 28-07-18 10:34 AM NULL 1 17 6 SH0064 28-07-18 10:34 AM NULL 1 18 6 SH0064 28-07-18 10:36 AM NULL 1 19 6 SH00081 28-07-18 11:05 AM NULL 1 20 6 NEB0141 28-07-18 11:06 AM NULL 1 21 6 SH00081 28-07-18 11:06 AM NULL 1 22 6 SH0064 28-07-18 11:06 AM NULL 1

Upvotes: 1

Views: 91

Answers (3)

Andrei Odegov
Andrei Odegov

Reputation: 3439

Check the following query that runs on the guessed test data.

DECLARE @attendanceBioMetric TABLE(
  employee_code INT,
  attendance_date_with_time DATETIME
);

INSERT @attendanceBioMetric(employee_code, attendance_date_with_time)
VALUES
  (1, '20180902 23:58'), (1, '20180903 07:32'),
  (2, '20180903 06:57'), (2, '20180903 15:47'),
  (3, '20180903 15:28'), (3, '20180904 00:03'),
  (1, '20180903 23:42'), (1, '20180904 07:17'),
  (2, '20180904 06:57'), (2, '20180904 15:47'),
  (3, '20180904 15:28'), (3, '20180905 00:03');

SELECT
  employee_code,
  CONVERT(DATE, MIN(attendance_date_with_time)) AS [DATE],
  MIN(attendance_date_with_time) [IN],
  MAX(attendance_date_with_time) [OUT]
FROM(
  SELECT
    *,
    (ROW_NUMBER()
       OVER(PARTITION BY employee_code
            ORDER BY attendance_date_with_time)-1)/2 ioseq
  FROM @attendanceBioMetric) a
GROUP BY employee_code, ioseq;

To try the query online, please click here.

Upvotes: 1

Attie Wagner
Attie Wagner

Reputation: 1362

Would the following work for you?

I Tested it using my data, but I think it would work.

    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]
,   case when day(MAX([attendance_date_with_time])) = day(MIN([attendance_date_with_time])) then MAX([attendance_date_with_time]) else
    when MAX([attendance_date_with_time]) is null then 
 MIN(DATEADD(day, 1, [attendance_date_with_time])) else MAX([attendance_date_with_time]) end [OUT-B]
 FROM attendanceBioMetric 
GROUP BY Employee_Code,CONVERT(DATE,([attendance_date_with_time]) ) 
order by DATE desc

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

Untested script.This is just an idea.you have to manage it.

;with CTE as
(
select empcode from FROM attendanceBioMetric 
where shift='B'

)
,CTE1 as
(
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 
)

select c1.* 
,case when c.employee_code is not null then ShiftB_LogOut
from CTE1 c1
left join cte c on c.employee_code=c1.employee_code
outer apply(select min([date])ShiftB_LogOut 
from CTE1 c2 
where c.employee_code=c1.employee_code 
and c1.date=datead(day,1,c2.date))c2

Upvotes: 1

Related Questions