I'm trying to display data from my table "MST_Permissionrequest" witch contain the data (_date, StartTime, EndTime, EmployeeID)
and from my table "MST_Attendance" witch contain the data (_date, EmpID, Times_In, Times_Out)
I need to display the data per date : for example for the date :06-09-2015
Date First_Time_In First_Times_Out Second_TimeIn
06-09-2015 06-09-2015 12:30:00 06-09-2015 13:30:00 06-09-2015 15:30:00
Second_TimeOut StartTimePermission EndTimePermission
06-09-2015 17:30:00 10:00 11:00
I tried the code below witch allow me to display the data from "MST_Permissionrequest" but how can display the others data from "MST_Attendance".
Information: if the employee has times_in and times_out in a specific date and he hasn't permission, the startTimePermission and EndTimePermission should take NULL value.
This is my code:
WITH per As
(
SELECT EmployeeID,
CAST(Emp_PermissionsRequest._Date As date) As PermissionDate,
StartTime,
EndTime,MSt_Permissions.Permissions as permission,
ROW_NUMBER() OVER (PARTITION BY EmployeeID, CAST(Emp_PermissionsRequest._Date As date) ORDER BY StartTime) As RNI,
ROW_NUMBER() OVER (PARTITION BY EmployeeID, CAST(Emp_PermissionsRequest._Date As date) ORDER BY EndTime DESC) As RNO ,
ROW_NUMBER() OVER (PARTITION BY EmployeeID, CAST(Emp_PermissionsRequest._Date As date) ORDER BY endtime DESC) As PER ,
ROW_NUMBER() OVER (PARTITION BY EmpID, CAST(Emp_Attendance._Date As date) ORDER BY times_in desc) As timesin
FROM dbo.Emp_PermissionsRequest
inner join MSt_Permissions on MSt_Permissions.ID=Emp_PermissionsRequest.PermissionID
left outer join Emp_Attendance on Emp_Attendance.EmpID=Emp_PermissionsRequest.EmployeeID
WHERE Month(Emp_PermissionsRequest._Date) = 6 And EmployeeID = 35 and PermissionStatus='Approve' )
SELECT
EmployeeID, PermissionDate,
Max(CASE WHEN RNI = 1 THEN StartTime ELSE Null END) As FirstStartTime,
Max(CASE WHEN RNI = 2 THEN StartTime ELSE Null END) As SecondStartTime,
Max(CASE WHEN RNI = 3 THEN StartTime ELSE Null END) As ThirdStartTime,
Max(CASE WHEN RNI = 4 THEN StartTime ELSE Null END) As LastStartTime,
Max(CASE WHEN RNO = 2 THEN EndTime ELSE Null END) As FirstEndTime,
Max(CASE WHEN RNO = 1 THEN EndTime ELSE Null END) As SecondEndTime,
Max(CASE WHEN RNO = 3 THEN EndTime ELSE Null END) As ThirdEndTime,
Max(CASE WHEN RNO = 4 THEN EndTime ELSE Null END) As LastEndTime,
Max(CASE WHEN PER = 1 THEN permission ELSE Null END) As Permission ,
Max(CASE WHEN timesin = 1 THEN Times_IN ELSE Null END) As times_in ,
CONVERT(varchar(3),sum(DATEDIFF(MINUTE, StartTime, EndTime) )/ 60)
+':' + RIGHT('0' + CONVERT(varchar(2),sum(DATEDIFF(MINUTE, StartTime, EndTime)) % 60),2) as durationpermission
FROM per left join Emp_Attendance on Emp_Attendance.EmpID= per.EmployeeID
GROUP BY EmployeeID ,PermissionDate