How can I get details from more than 1 table using with

See more:

SQL

SQL-Server

SQL-Server-2008R2

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, --Attendancedate, 
	

	  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 --,