Merhabalar bu kısa makalede sizlere Bordro Plus ürününde personel kartlarında tekrarlayan kayıtları, SQL Cursor ile TC kimlik numarasına göre filtreleyip listeleyebileceğimiz bir kod bloğunu paylaşacağım.

CREATE TABLE #LocalRempLref(LREF int)DECLARE @TTFNO VARCHAR(21)DECLARE CRS_PERSONAL CURSOR FORSELECT DISTINCT TTFNOFROM LH_001_PERSONWHERE TYP = 1OPEN CRS_PERSONALFETCH NEXT FROM CRS_PERSONAL INTO @TTFNOWHILE @@FETCH_STATUS =0	BEGIN	INSERT INTO #LocalRempLref (LREF)		SELECT TOP(1) LREF FROM LH_001_PERSON WHERE TTFNO = @TTFNO AND (TYP = 1) AND ((FIRMNR BETWEEN -1 AND 2) OR (FIRMNR BETWEEN 4 AND 11))		FETCH NEXT FROM CRS_PERSONAL INTO @TTFNO	ENDCLOSE CRS_PERSONALDEALLOCATE CRS_PERSONALSELECT P.LREF AS 'PersonalID',P.CODE AS 'Code',P.NAME AS 'FirstName',P.SURNAME AS 'LastName',PD.IDTCNO AS 'IdNumber',P.SEX AS 'Gender',P.EDUCATION AS 'EducationStatus',(SELECT TOP(1) ASSG.TITLE FROM LH_001_ASSIGN ASSG WHERE P.LREF = ASSG.PERREF AND (ASSG.LNNR <= 1) AND (ASSG.JOBLNNR <= 1) ORDER BY ASSG.LREF DESC) AS 'Mission',PD.PROFESSION AS 'Position',JB.DESC_ AS 'Job',PD.MILTSTATUS AS 'MilitaryStatus',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = 2 AND F.LREF = C.CARDREF) AS 'TelNo',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = 3 AND F.LREF = C.CARDREF) AS 'MobilNo',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = 6 AND F.LREF = C.CARDREF) AS 'EMail',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = -1 AND C.LNNR = 9 AND F.LREF = C.CARDREF) AS 'City',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = -1 AND C.LNNR = 8 AND F.LREF = C.CARDREF) AS 'Town',(SELECT TOP(1) C.EXP1 + ' ' +C.EXP2  AS MAIL FROM LH_001_CONTACT C WHERE C.TYP = 1 AND F.LREF = C.CARDREF) AS 'Address' INTO #LocalTempPerson FROM LH_001_PERSON P LEFT OUTER JOIN LH_001_TYPEDEF JB ON P.JOBREF = JB.NR AND (JB.TYP = -44)LEFT OUTER JOIN LH_001_FAMILY F ON P.LREF = F.PERREF AND (F.RELATION = 0)LEFT OUTER JOIN LH_001_PERIDINF PD ON F.LREF = PD.LREFWHERE (P.TYP = 1) AND ((P.FIRMNR BETWEEN -1 AND 2) OR (P.FIRMNR BETWEEN 4 AND 11)) AND P.LREF IN(SELECT LREF FROM #LocalRempLref)SELECT * FROM #LocalTempPersonDROP TABLE #LocalTempPersonDROP TABLE #LocalRempLref