LOGO Bordro Plus SQL Personel Listesi

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