YAPI KREDİ BANKASI IK,BORDRO,GO PLUS, PErsonel Listesi ŞAblonuna göre T-SQL

[LsuErsoy]

Merhaba Arkadaşlar ;

İhtiyac dolayısı ile yazdığım YKB, YAPI KREDİ LOGO IK,BORDRO,GO PLUS Personel Listesi T-SQL kodu aşağıdaki gibidir.

Kod: [Seç]


SELECT
PER.TTFNO AS [TC KİMLİK NO],
PER.NAME+' '+ PER.SURNAME AS [AD SOYAD],
 '' [İŞ YERİ ADI],
 '' AS [İŞ ADRESİ-DETAY],
 '' AS [İŞ ADRESİ-İLÇE],
 '' AS [İŞ ADRESİ-İL],
 '' AS [POSTA KODU],
'' AS [İŞ TELEFONU],
ISNULL(ILETISIM1.ADRES1,'Veri Girilmemiş') + ' ' +
ISNULL(ILETISIM5.ADRES2,'Veri Girilmemiş') [EV ADRESİ-DETAY],
ISNULL(ILETISIM6.ILCE,'Veri Girilmemiş') [EV ADRESİ-İLÇE],
ISNULL(ILETISIM7.IL,'Veri Girilmemiş') [EV ADRESİ-İL],
ISNULL(ILETISIM2.EV_TELEFONU,'Veri Girilmemiş')[EV TEL NO],
ISNULL(ILETISIM3.GSM,'Veri Girilmemiş')[CEP TELEFON NO],
ISNULL(ILETISIM4.E_POSTA,'Veri Girilmemiş') [E-MAIL]
 FROM LH_001_PERSON AS PER
      LEFT OUTER JOIN LH_001_FAMILY FAM ON FAM.PERREF=PER.LREF
      LEFT OUTER JOIN LH_001_CONTACT AS CON ON CON.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS ADRES1 , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=1 AND LNNR=1) ILETISIM1  ON ILETISIM1.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS EV_TELEFONU , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=2 AND LNNR=1) ILETISIM2  ON ILETISIM2.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS GSM, CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=3 AND LNNR=1) ILETISIM3 ON ILETISIM3.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS E_POSTA , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=6 AND LNNR=1) ILETISIM4 ON ILETISIM4.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP2 AS ADRES2 , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=1 AND LNNR=1) ILETISIM5  ON ILETISIM5.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS ILCE , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=-1 AND LNNR=8) ILETISIM6  ON ILETISIM6.CARDREF=FAM.LREF
      LEFT OUTER JOIN (SELECT DISTINCT CON.EXP1 AS IL , CON.CARDREF FROM LH_001_CONTACT AS CON Where CON.TYP=-1 AND LNNR=9) ILETISIM7  ON ILETISIM7.CARDREF=FAM.LREF
      WHERE FAM.RELATION=0 AND PER.STATUS=1     
 GROUP BY
  PER.TTFNO
 ,PER.NAME
 ,PER.SURNAME
 ,FAM.LREF
 ,ILETISIM1.ADRES1
 ,ILETISIM5.ADRES2
 ,ILETISIM2.EV_TELEFONU
 ,ILETISIM3.GSM
 ,ILETISIM4.E_POSTA
 ,ILETISIM6.ILCE
 ,ILETISIM7.IL