Click here to Skip to main content
15,917,862 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 30-Aug-17 1:32am

1 solution

To avoid duplicates just use the distinct function and accepted use where caluse:

select distinct foo from table where status = 'Accepted'.

I cant do the whole query because there is alot of data.

Hope i helped.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900