Как захватить только" принятый " статус отпуска и избежать повторяющихся записей

---

Привет ,
Я работаю над отчетом о заработной плате, и у меня есть дубликаты записей в моем сохраненном Proc, как показано ниже,и мне нужно фиксировать только принятый статус и избегать таких дубликатов записей

Что я уже пробовал:

--Select * from IntranetRelease.dbo.EmployeeDetails Where EmpName Like 'Anand%'


select Row_Number() over (order by e.EmployeeCode,AttendanceDate desc) as 'SNo',e.EmployeeName as 'EmpName',e.EmployeeCode as 'EmpId',
          d.DepartmentFName as 'DeptName',CONVERT(VARCHAR, a.AttendanceDate, 110) as 'AttendanceDate',s.ShiftName as 'Shift',Ltrim (Right(convert(varchar(20),convert(datetime, a.InTime,0),100),7)) as 'InTime',   
         convert(char(5),cast(cast((a.Duration- s.ShiftDuration) as integer)/60 as varchar)+':'+CAST(cast((a.Duration- s.ShiftDuration) as integer)%60
         as varchar),108) as 'OT',convert(char(5),cast(cast(a.Duration as integer)/60 as varchar)+':'+CAST(cast(a.Duration as integer)%60 as varchar),108)
         as 'TotDur',a.Duration,
         CASE WHEN  a.InTime> a.OutTime THEN DATEDIFF (MINUTE,CAST(a.InTime as datetime),DateAdd(day,1,CAST(a.OutTime AS datetime)))
 ELSE DATEDIFF(MINUTE, a.InTime , a.OutTime) END AS TotalMinuteDiff,
         convert(char(5),cast(cast(a.LateBy as integer)/60 as varchar)+':'+CAST(cast(a.LateBy as integer)%60 as varchar),108) as 'LateBy',
         a.Status,
         CASE WHEN substring(cast(a.PunchRecords as nvarchar(max)), (len(cast(a.PunchRecords as nvarchar(max))) - 4), case when (len(cast(a.PunchRecords as nvarchar(max)))>0) then (len(cast(a.PunchRecords as nvarchar(max))) - 5) else (len(cast(a.PunchRecords as nvarchar(max)))) end )='(in),' THEN substring(cast(a.PunchRecords as nvarchar(max)), 1, (len(cast(a.PunchRecords as nvarchar(max))) - 10))ELSE a.PunchRecords END as PunchRecords,
         
         --a.PunchRecords as ReportPunchRecords,
         IntranetReleaseNew.dbo.GetPunchRecords(a.PunchRecords) as ReportPunchRecords,
         IntranetReleaseNew.dbo.GetLastPunchRecord(a.PunchRecords) as OutTime,
         CASE WHEN la.LeaveType is null THEN '' ELSE la.LeaveType END AS 'LeaveType',
         CASE WHEN la.LeaveStatus is null THEN '' ELSE la.LeaveStatus END AS 'LeaveStatus'
         
         INTO #ResultPresent119
         
         FROM [eSSLSmartofficeNew].dbo.Employees e inner join
         [eSSLSmartofficeNew].dbo.AttendanceLogs a on e.EmployeeId=a.EmployeeId inner join 
         [eSSLSmartofficeNew].dbo.Departments d on d.DepartmentId=e.DepartmentId inner join
         [eSSLSmartofficeNew].dbo.Shifts s on s.ShiftId=a.ShiftId LEFT JOIN
	   IntranetReleaseNew.dbo.EmployeeDetails Ed ON Ed.EmpId COLLATE SQL_Latin1_General_CP1_CI_AS = e.employeecode COLLATE						SQL_Latin1_General_CP1_CI_AS LEFT JOIN  
	   IntranetReleaseNew.dbo.LeaveApplication la ON la.EmpId COLLATE SQL_Latin1_General_CP1_CI_AS = e.employeecode COLLATE						SQL_Latin1_General_CP1_CI_AS     
	   AND a.AttendanceDate >=la.leavefromdate AND a.AttendanceDate <=la.leaveTodate         
 where 
            e.EmployeeName not like '%del_%' and e.EmployeeCode like '%S%' and  (ltrim(rtrim(a.Status))='Present' or ltrim(rtrim(a.Status))='WeeklyOff Present ' or (ltrim(rtrim(a.Status))='Present' or ltrim(rtrim(a.Status))='WeeklyOff ')) and
            a.AttendanceDate>='08-21-2017' and a.AttendanceDate<='08-21-2017' and Ed.Status=0  and e.EmployeeCode ='SS339'
            and a.AttendanceDate not in(select HolidayDate from esslsmartofficenew.dbo.Holidays)
  
	UPDATE #ResultPresent119 SET PunchRecords = REPLACE(CAST(PunchRecords AS NVARCHAR(MAX)),'(out),','(out);')
	 
	;with tmp(SNo,EmpId,EmpName,DeptName,AttendanceDate,Shift, InTime, OutTime,Duration,TotalMinuteDiff,ReportPunchRecords,SinglePunch,PunchRecords,Status,LeaveType,LeaveStatus) as (
	select SNo, EmpId,EmpName,DeptName,AttendanceDate,Shift, InTime, OutTime,Duration,TotalMinuteDiff,ReportPunchRecords, LEFT(CAST(PunchRecords AS VARCHAR(MAX)), CHARINDEX(';',CAST(PunchRecords AS VARCHAR(MAX))+';')-1),
		STUFF(CAST(PunchRecords AS VARCHAR(MAX)), 1, CHARINDEX(';',CAST(PunchRecords AS VARCHAR(MAX))+';'), ''),Status,LeaveType,LeaveStatus
	from #ResultPresent119
	union all
	select SNo,EmpId,EmpName,DeptName,AttendanceDate,Shift, InTime, OutTime,Duration,TotalMinuteDiff,ReportPunchRecords, LEFT(PunchRecords, CHARINDEX(';',CAST(PunchRecords AS VARCHAR(MAX))+';')-1),
		STUFF(CAST(PunchRecords AS VARCHAR(MAX)), 1, CHARINDEX(';',CAST(PunchRecords AS VARCHAR(MAX))+';'), ''),Status,LeaveType,LeaveStatus
	from tmp
	where PunchRecords > ''
	)
	select SNo,EmpId, EmpName,DeptName,AttendanceDate,Shift,InTime, OutTime,Duration,TotalMinuteDiff,convert(char(5),cast(cast(TotalMinuteDiff as integer)/60 as varchar)+':'+CAST(cast(TotalMinuteDiff as integer)%60 as varchar),108) as 'TotalHrs',ReportPunchRecords,SinglePunch,Status,LeaveType,LeaveStatus 
	INTO #Result116Present
	from tmp
	OPTION (maxrecursion 0)

	select  DISTINCT SNo,EmpId,EmpName,DeptName,AttendanceDate,Shift, InTime, OutTime,cast(ReportPunchRecords as nvarchar(max))as 'Punch Records', 


	convert(char(5),cast(cast(sum(TimeDifference) over (partition by EmpId,SNo) as integer)/60 as varchar)+':'+CAST(cast(sum(TimeDifference) over (partition by EmpId,SNo) as integer)%60 as varchar),108) as 'InDur',
	convert(char(5),cast(cast(TotalMinuteDiff-sum(TimeDifference) over (partition by EmpId,SNo) as integer)/60 as varchar)+':'+CAST(cast(TotalMinuteDiff-sum(TimeDifference) over (partition by EmpId,SNo) as integer)%60 as varchar),108) as 'OutDur',
TotalHrs,

	Status,LeaveType,LeaveStatus  from (SELECT SNo,
		EmpId,EmpName,DeptName,AttendanceDate,Shift, InTime, OutTime,Duration,TotalMinuteDiff,TotalHrs, ReportPunchRecords,SinglePunch,
		SUBSTRING(SinglePunch, 0, 6) AS 'InTimes',
		SUBSTRING(SinglePunch, 11, 5) AS 'OutTimes',
		--DATEDIFF (MINUTE,CAST(SUBSTRING(SinglePunch, 0, 6) as Time),CAST(SUBSTRING(SinglePunch, 11, 5) AS TIME)) AS 'TimeDifference',
		CASE WHEN  CAST(SUBSTRING(SinglePunch, 0, 6) as Time)> CAST(SUBSTRING(SinglePunch, 11, 5) AS TIME) THEN  
	 DATEDIFF (MINUTE,CAST(SUBSTRING(SinglePunch, 0, 6) as datetime),DateAdd(day,1,CAST(SUBSTRING(SinglePunch, 11, 5) AS datetime)))
	 ELSE DATEDIFF (MINUTE,CAST(SUBSTRING(SinglePunch, 0, 6) as Time),CAST(SUBSTRING(SinglePunch, 11, 5) AS TIME)) END AS 'TimeDifference',
		Status,LeaveType,LeaveStatus
	FROM 
	#Result116Present) as q2  
DROP TABLE #Result116Present  
DROP TABLE #ResultPresent119

EmpId	EmpName	SNo	   DeptName	AttendanceDate	Shift	InTime	OutTime	     Punch Records	        InDur	OutDur	TotalHr Status	 LeaveType   LeaveStatus
SS339	Anandhi	1	HUMAN RESOURCES	08-21-2017	General	2:54PM	8:47PM 	2:54PM (in),8:47PM (out)	5:53 	0:0  	5:53 	Present	Sick Leave	Rejected
SS339	Anandhi	2	HUMAN RESOURCES	08-21-2017	General	2:54PM	8:47PM 	2:54PM (in),8:47PM (out)	5:53 	0:0  	5:53 	Present	Sick Leave	Accepted

---