本文介绍了如何仅捕获“已接受”离开状态并避免重复输入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在处理PayRoll报告，我在存储的Proc中有重复的条目，如下所示，我只需捕获接受状态并避免此类重复条目
我尝试过：
Hi ,
I am working on PayRoll report and I have duplicate entries in my stored Proc like the below,and I need to capture only Accepted Status and avoid Such duplicate entries
What I have tried:
--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推荐答案
这篇关于如何仅捕获“已接受”离开状态并避免重复输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！