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
0 Yorumlar
Yorumunuz için çok teşekkür ederim. En kısa zamanda geri dönüş yapacağım.