BordroPlus için sorgu, rapor, eklenti paylaşımı

[yken]

BordroPlus için sorgu, rapor, eklenti paylaşımını burada yapabiliriz.

************
select
YEAR(P.PERDBEG) YIL
,MONTH(P.PERDBEG) AY
,D.NAME ISYERI
,S.CODE KODU
,S.NAME ADI
,S.SURNAME SOYADI
,INF.IDTCNO TCKNO
,CASE S.yasak WHEN 1 THEN 'ERKEK' ELSE 'KADIN' END  CINSIYET
,CASE S.TYP  WHEN 1 THEN 'AKTIF' ELSE 'PASIF' END STATU
,CASE LAW.SSKSTATUS WHEN  1 THEN 'Normal'WHEN  2 THEN 'Emekli'WHEN  3 THEN 'Çirak'WHEN  4 THEN 'Stajyer'WHEN  5 THEN 'Yabanci' WHEN  6 THEN 'Bag-kur(çalisan)'WHEN  7THEN 'Bag-kur(emekli)'  WHEN  8 THEN 'Diger'else 'Tanimsiz' end  as 'SGK STATU'
,CONVERT(VARCHAR(10),S.INDATE, 104) 'GIRIS TARIHI'
,CONVERT(VARCHAR(10),S.BIRTHDATE, 104) 'DOGUM TARIHI'
,(SELECT EXP FROM LH_001_TYPEDEF WHERE TYP=-44 AND NR=S.JOBREF) 'MESLEK KODU'
,(SELECT DESC_ FROM LH_001_TYPEDEF WHERE TYP=-44 AND NR=S.JOBREF) 'MESLEK ADI'
,(SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=0 AND NR=1 AND PREF=P.LREF) 'SGK GUN'
---,(SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=0 AND NR=2 AND PREF=P.LREF) 'VERGI GUN'
,(SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=0 AND NR=3 AND PREF=P.LREF) 'CALISMA GUN'
,P.WAGE_WAGE UCRETI
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=1 AND PREF=P.LREF),0) 'Gün Normal'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=2 AND PREF=P.LREF),0) 'Gün Hafta Tatili'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=3 AND PREF=P.LREF),0) 'Gün Resmi Tatil'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=4 AND PREF=P.LREF),0) 'Gün Ücretli İzin'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=5 AND PREF=P.LREF),0) 'Gün Çalışılan Tatil'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=6 AND PREF=P.LREF),0) 'Gün Ücretsiz İzin'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=7 AND PREF=P.LREF),0) 'Gün Yıllık İzin'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=8 AND PREF=P.LREF),0) 'Gün Doğum İzni'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=9 AND PREF=P.LREF),0) 'Gün Ölüm İzni'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=10 AND PREF=P.LREF),0) 'Gün Mazeretsiz İzin '
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=11 AND PREF=P.LREF),0) 'Gün Mazeret İzni'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=12 AND PREF=P.LREF),0) 'Gün Evlilik İzni'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=13 AND PREF=P.LREF),0) 'Gün Mazeret İzni2'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=14 AND PREF=P.LREF),0) 'Gün Mazeretsiz İzin2'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=15 AND PREF=P.LREF),0) 'Gün Raporlu'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=16 AND PREF=P.LREF),0) 'Gün Raporlu2'
, COALESCE((SELECT DAY_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=17 AND PREF=P.LREF),0) 'Gün Ücretli Rapor'

, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=1 AND PREF=P.LREF),0) 'Brüt Normal'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=2 AND PREF=P.LREF),0) 'Brüt Hafta Tatili'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=3 AND PREF=P.LREF),0) 'Brüt Resmi Tatil'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=4 AND PREF=P.LREF),0) 'Brüt Ücretli İzin'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=5 AND PREF=P.LREF),0) 'Brüt Çalışılan Tatil'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=6 AND PREF=P.LREF),0) 'Brüt Ücretsiz İzin'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=7 AND PREF=P.LREF),0) 'Brüt Yıllık İzin'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=8 AND PREF=P.LREF),0) 'Brüt Doğum İzni'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=9 AND PREF=P.LREF),0) 'Brüt Ölüm İzni'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=10 AND PREF=P.LREF),0) 'Brüt Mazeretsiz İzin '
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=11 AND PREF=P.LREF),0) 'Brüt Mazeret İzni'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=12 AND PREF=P.LREF),0) 'Brüt Evlilik İzni'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=13 AND PREF=P.LREF),0) 'Brüt Mazeret İzni2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=14 AND PREF=P.LREF),0) 'Brüt Mazeretsiz İzin2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=15 AND PREF=P.LREF),0) 'Brüt Raporlu'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=16 AND PREF=P.LREF),0) 'Brüt Raporlu2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=17 AND PREF=P.LREF),0) 'Brüt Ücretli Rapor'



, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=1 AND PREF=P.LREF),0) 'B1a '
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=2 AND PREF=P.LREF),0) 'B2b'

, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=0 AND PREF=P.LREF),0) 'B1 '
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=4 AND PREF=P.LREF),0) 'B4'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=6 AND PREF=P.LREF),0) 'B5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=7 AND PREF=P.LREF),0) 'B6'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=41 AND PREF=P.LREF),0) 'B7'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=42 AND PREF=P.LREF),0) 'B8'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=61 AND PREF=P.LREF),0) 'B9'



, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=6 AND PREF=P.LREF),0) 'Baa5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=7 AND PREF=P.LREF),0) 'Baa6'



, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=6 AND PREF=P.LREF),0) 'Ba5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=7 AND PREF=P.LREF),0) 'Ba6'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=41 AND PREF=P.LREF),0) 'Ba7'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=42 AND PREF=P.LREF),0) 'Ba8'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=61 AND PREF=P.LREF),0) 'Ba9'


, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=6 AND PREF=P.LREF),0) 'Bb5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=7 AND PREF=P.LREF),0) 'Bb6'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=41 AND PREF=P.LREF),0) 'Bb7'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=42 AND PREF=P.LREF),0) 'Bb8'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=61 AND PREF=P.LREF),0) 'Bb9'


, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=6 AND PREF=P.LREF),0) 'Bc5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=7 AND PREF=P.LREF),0) 'Bc6'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=41 AND PREF=P.LREF),0) 'Bc7'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=42 AND PREF=P.LREF),0) 'Bc8'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=61 AND PREF=P.LREF),0) 'Bc9'


, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=6 AND PREF=P.LREF),0) 'Bd5'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=7 AND PREF=P.LREF),0) 'Bd6'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=41 AND PREF=P.LREF),0) 'Bd7'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=42 AND PREF=P.LREF),0) 'Bd8'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=6 AND NR=61 AND PREF=P.LREF),0) 'Bd9'


, COALESCE((SELECT HOUR_ FROM LH_001_PNTLINE WHERE TYP=2 AND NR=1 AND PREF=P.LREF),0) 'Saat Normal Mesai'
, COALESCE((SELECT HOUR_ FROM LH_001_PNTLINE WHERE TYP=2 AND NR=2 AND PREF=P.LREF),0) 'Saat Pazar Mesaisi'
, COALESCE((SELECT HOUR_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=3 AND PREF=P.LREF),0) 'Saat Resmi Tatil Mesaisi'
, COALESCE((SELECT HOUR_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=4 AND PREF=P.LREF),0) 'Saat Fazla Mesai (4)'
, COALESCE((SELECT HOUR_ FROM LH_001_PNTLINE WHERE TYP=1 AND NR=5 AND PREF=P.LREF),0) 'Saat Gece Vardiyası'





, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=2 AND NR=1 AND PREF=P.LREF),0) 'Brüt Normal Mesai'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=2 AND NR=2 AND PREF=P.LREF),0) 'Brüt Pazar Mesaisi'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=2 AND NR=3 AND PREF=P.LREF),0) 'Brüt Resmi Tatil Mesaisi'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=2 AND NR=4 AND PREF=P.LREF),0) 'Brüt Fazla Mesai (4)'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=2 AND NR=5 AND PREF=P.LREF),0) 'Brüt Gece Vardiyası'

, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=1 AND PREF=P.LREF),0) 'S.Y. Yemek Yardımı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=2 AND PREF=P.LREF),0) 'S.Y. Ulaşım Yardımı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=3 AND PREF=P.LREF),0) 'S.Y. Aile Yardımı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=4 AND PREF=P.LREF),0) 'S.Y. Çocuk Yardımı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=5 AND PREF=P.LREF),0) 'S.Y. Şahıs Sigortası'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=3 AND NR=6 AND PREF=P.LREF),0) 'S.Y. Bireysel Emeklilik Sigortası'

, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=1 AND PREF=P.LREF),0) 'E.O. İkramiye'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=2 AND PREF=P.LREF),0) 'E.O. Kıdem Tazminatı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=3 AND PREF=P.LREF),0) 'E.O. İhbar Tazminatı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=4 AND PREF=P.LREF),0) 'E.O. Harcırah'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=5 AND PREF=P.LREF),0) 'E.O. Vergi İadesi'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=6 AND PREF=P.LREF),0) 'E.O. Nema Ödemesi'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=7 AND PREF=P.LREF),0) 'E.O. Yıllık İzin Ücreti'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=8 AND PREF=P.LREF),0) 'E.O. Prim'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=9 AND PREF=P.LREF),0) 'E.O. Kullanılmayan Yıllık izin'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=10 AND PREF=P.LREF),0) 'E.O. Ek Ödeme '
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=11 AND PREF=P.LREF),0) 'E.O. Fazla Mesai'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=4 AND NR=12 AND PREF=P.LREF),0) 'E.O. Pazar Mesaisi'

, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=1 AND PREF=P.LREF),0) 'E.K. Şahıs Sigortası'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=2 AND PREF=P.LREF),0) 'E.K. Bireysel Emeklilik Sigortası'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=3 AND PREF=P.LREF),0) 'E.K. Ek Kesinti-1'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=4 AND PREF=P.LREF),0) 'E.K. İhbar Tazminatı'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=8 AND PREF=P.LREF),0) 'E.K. Masraf'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=9 AND PREF=P.LREF),0) 'E.K. Yasal Kesintiler'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=10 AND PREF=P.LREF),0) 'E.K. İcra'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=11 AND PREF=P.LREF),0) 'E.K. Bes'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=12 AND PREF=P.LREF),0) 'E.K. Avans'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=13 AND PREF=P.LREF),0) 'E.K. Trafik Cezası '
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=14 AND PREF=P.LREF),0) 'E.K. Nafaka'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=15 AND PREF=P.LREF),0) 'E.K. İcra2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=16 AND PREF=P.LREF),0) 'E.K. Trafik Cezası2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=17 AND PREF=P.LREF),0) 'E.K. Avans2'
, COALESCE((SELECT GROSSAM FROM LH_001_PNTLINE WHERE TYP=1 AND NR=18 AND PREF=P.LREF),0) 'E.K. Nafaka2'

,BALN_PAYMENTS_PTD 'ÖDEMELER TOPLAMI'
,BALN_TAXNBASE_YTD  'VERGI MATRAHI'
,BALN_TAXNORM_YTD+BALN_MINWAGEDISC_PTD 'VERGİ KESINTISI TUTARI'
,BALN_MINWAGEDISC_PTD AGI
,BALN_STAMPBASE_PTD 'DAMGA MATRAHI'
,BALN_STAMP 'DAMGA VERGİSİ'
,BALN_SSKNBASE_PTD 'SGK MATRAH'
,BALN_SECEPLE_PTD 'SGK ISCI'
,BALN_SECEPLR_PTD 'SGK ISVEREN'
,BALN_SSKPRIMGOVDEPLAW 'SGK HAZINE PAYI'
,BALN_NETWAGE 'NET ODEME'



from LH_001_PNTCARD AS P
INNER JOIN LH_001_PERSON AS S ON S.LREF=P.PERREF
INNER JOIN L_CAPIDIV AS D ON D.NR=P.LOCNR
INNER JOIN LH_001_FAMILY FAM ON   S.LREF = FAM.PERREF  AND RELATION = 0
INNER JOIN LH_001_PERIDINF  INF ON   INF.LREF =   FAM.IDREF
INNER JOIN LH_001_LAWCHG LAW ON S.LREF = LAW.PERREF AND LAW.ENDDATE IS NULL

*******************

select
(SELECT CODE FROM LH_001_PERSON WHERE LREF=F.PERREF) Kodu
,(SELECT name FROM LH_001_PERSON WHERE LREF=F.PERREF) Adı
,(SELECT SURNAME FROM LH_001_PERSON WHERE LREF=F.PERREF) Soyadı
,case RELATION WHEN 1 THEN 'Eşi' when 2 then 'Cocuk' when 3 then 'Anne' else 'Baba' end Yakınlık
,f.name Adi
,f.SURNAME Soyadi
,case yasak when 1 then 'Bay' else 'Bayan' end Cinsiyet
,case EDUCSTAT WHEN 0 then  'Tanımsız' when 1 then 'Evet' else 'Hayır' end OgrencilikDurumu
,case SUPPORTED when 0 then 'Evet' else 'Hayir' end BakmaklaYukumlu
,case MINWGDISCSTAT when 0 then 'Evet' else 'Hayir' end AgiDurumu
,(select BIRTHDATE from LH_001_PERIDINF WHERE LREF=F.IDREF) DogumTarihi

from LH_001_FAMILY AS F

where RELATION<>0

GO
******************

(SELECT name FROM LH_001_FAMILY WHERE LREF=F.CARDREF) Adı
,(SELECT SURNAME FROM LH_001_FAMILY WHERE LREF=F.CARDREF) Soyadı
,CASE HTYPE
WHEN 1 THEN 'Vizite'
WHEN 2 THEN 'Rapor'
WHEN 3 THEN 'Konrol'
WHEN 4 THEN 'Teshis'
WHEN 5 THEN 'Tetkik'
WHEN 6 THEN 'İşKazası'
WHEN 7 THEN 'MeslekHastalıgı' end Tip
,f.EVALDATE IslemTarihi
,f.PERIOD1 Baslangic
,f.PERIOD2 Bitiş
,f.OFFDAYS1 Sure
,f.exp1 + f.exp2 Acıklama


from
LH_001_HEALTH AS F
where (SELECT name FROM LH_001_FAMILY WHERE LREF=F.CARDREF)<>'x'

**********************

[MYERP]

Nasıl bir sorgu, rapor, eklenti ?

[yken]

Bordrodaki şeyler standart, çoğu rapor gereksiz, işlevini yitirmiş, programın bir sürü özellilkleri zaten geçmişten kalmış.
Halen TTF, Nema ödemesi, 4 aylık disket, batmış Pamukbank, Koçbank Banka ödeme doslayarı var, zaten çalışmıyor da, bir işe yaramıyorda.
Setup içinde taa 2006 yılında kalmış Bütçe Eğitimi  videosu var, halen Dos ortamından aktarım için 100 mb lık Dos convert exesi var yani kısaca bu program biraz çağın gerisinde kalmış.
LOGO'yu böyle bilmiyorduk ama böyleymiş.
Bu yüzden programa işlev katacak, sorgu olabilir, rapor olabilir ne olursa olsun herbiri her zaman lazım olabiliyor.

[ehliyet]

Faydası olur mu bilmem ama benim yazdığım bir sorgu var umarım işine yarar.

Kod: [Seç]

ALTER VIEW [dbo].[GENELRAPOR_ISCIKIS] AS
 

SELECT

P.CODE AS SICILNO,
P.TTFNO AS TCKIMLIKNO,
P.NAME + ' ' +P.SURNAME  AS ADISOYADI,
P.FIRMNR AS FİRMANO,
L_CAPIDIV.NAME AS İŞYERİ,
L_CAPIDEPT.NAME AS DEPARTMANADI,
CAP.NAME AS BİRİM,
P.INDATE AS İŞEGİRİŞTARİHİ,
P.OUTDATE AS İŞTENÇIKIŞTARİ,
LH_002_QUALFDEF.NAME AS UNVANI,
CASE LAW.SSKSTATUS
      WHEN  1 THEN '4/a Normal'
      WHEN  2 THEN '4/a Emekli'
      WHEN  3 THEN '4/a Çırak'
      WHEN  4 THEN '4/a Stajyer'
      WHEN  5 THEN '4/a Yabancı'
      WHEN  6 THEN '4/b Bağ-kur(çalışan)'
      WHEN  7 THEN '4/b Bağ-kur(emekli)'
      WHEN  8 THEN 'Diğer'
      else 'Tanımsız'
end  as SosyalGuvenlikStatusu,
CASE P.EDUCATION
   WHEN 0 THEN 'Girilmemiş'
   WHEN 1 THEN 'Okuryazar'
   WHEN 2 THEN 'İlköğretim'
   WHEN 3 THEN 'Ortaöğretim'
   WHEN 4 THEN 'Lise'
   WHEN 5 THEN 'Meslek Yüksek Okulu'
   WHEN 6 THEN 'Üniversite'
   WHEN 7 THEN 'Yüksek Lisans'
   WHEN 8 THEN 'Doktora'

END AS OgretimDurumu,
P.BIRTHDATE AS DOĞUMTARİHİ,
CASE P.yasak
WHEN 1 THEN 'ERKEK'
WHEN 2 THEN 'KIZ'
ELSE  ' GİRİŞMEMİŞ'
END AS CİNSİYET,
CASE B.STATUS
   WHEN 1 THEN 'Evli'
   WHEN 2 THEN 'Bekar'
   ELSE 'Tanımsız'
END AS MEDENİHALİ,

CASE B.BLOODGROUP
   WHEN 1 THEN '0 RH(+)'
   WHEN 2 THEN '0 RH(-)'
   WHEN 3 THEN 'A RH(+)'
   WHEN 4 THEN 'A RH(-)'
   WHEN 5 THEN 'B RH(+)'
   WHEN 6 THEN 'B RH(-)'
   WHEN 7 THEN 'AB RH(+)'
   WHEN 8 THEN 'AB RH(-)'
ELSE 'Belirtilmemiş' END AS KANGRUBU,
ADRES.EXP1 + ' ' + ADRES.EXP2 AS ADRES,
GSM.EXP1 + ' ' + GSM.EXP2 AS GSM,
TEL.EXP1 + ' ' + TEL.EXP2 AS TEL,
ARA.EXP1 + ' ' + ARA.EXP2 AS ACIL_DURUMDA_ARANACAK_KISI   ,
--ISNULL((SELECT TOP 1 C.EXP1+' '+C.EXP2 FROM LH_001_CONTACT C WHERE C.CARDREF=EVN.IDREF AND C.TYP=1),'Belirtilmemiş') AS ADRES,
--ISNULL((SELECT TOP 1 C.EXP1+' '+C.EXP2 FROM LH_001_CONTACT C WHERE C.CARDREF=EVN.IDREF AND C.TYP=2),'Belirtilmemiş') AS GSM,
--ISNULL((SELECT TOP 1 C.EXP1+' '+C.EXP2 FROM LH_001_CONTACT C WHERE C.CARDREF=EVN.IDREF AND C.TYP=3),'Belirtilmemiş') AS ACILARANACAKKISI,
CASE P.TYP
WHEN 1 THEN 'Çalışıyor'
WHEN 2 THEN 'Ayrılmış' END AS DURUMU ,
CASE B.DRIVINGCLASS
WHEN 1 THEN 'YOK'
WHEN 2 THEN 'A1'
WHEN 3 THEN 'A2'
WHEN 4 THEN 'B'
WHEN 5 THEN 'C'
WHEN 6 THEN 'D'
WHEN 7 THEN 'E'
WHEN 8 THEN 'F'
WHEN 9 THEN 'G'
WHEN 10 THEN 'H'
WHEN 11 THEN 'K'
END AS EHLIYETSINIFI  ,
CASE B.MILTSTATUS
WHEN 0 THEN 'BELİRTİLMEMİŞ'
WHEN 1 THEN 'TECİLLİ'
WHEN 2 THEN 'TERHİS'
WHEN 3 THEN 'MUAF'
END AS ASKERLIKDURUMU  ,
B.BIRTHPLACE AS DOGUMYERI  ,
B.CITY AS KAYITLIOLDUGUIL  ,
PER.INSSTARTDATE AS SG_BASLANGIC   ,
PER.WAGE_WAGE AS BÜRÜTÜCRET,
LAW.EXP AS İŞDURUMU,
CASE L.REPSTATS16
WHEN 01 THEN 'Deneme süreli iş sözleşmesinin işverence feshi'
WHEN 02 THEN 'Deneme süreli iş sözleşmesinin işçi tarafından feshi'
WHEN 03 THEN 'Belirsiz süreli iş sözleşmesinin işçi tarafından feshi'
WHEN 04 THEN 'Belirsiz süreli iş sözleşmesinin işveren tarafından feshi'
WHEN 05 THEN 'BelirLİ süreli iş sözleşmesinin sona ermesi'
WHEN 08 THEN 'Emeklilik (yaşlılık) veya topran ödeme nedeni ile fesih'
WHEN 09 THEN 'Malülen emeklilik nedeniyle'
WHEN 10 THEN 'Ölüm'
WHEN 12 THEN 'Askerlik'
WHEN 13 THEN 'Kadın işçinin evlenmesi'
WHEN 14 THEN 'Emeklilik için yaş dışında diğer şartların tamamlanması'
WHEN 16 THEN 'Nakil'
WHEN 22 THEN 'Diğer Nedenler'
WHEN 24 THEN 'İşçi tarafından sağlık nedeniyle fesih'
WHEN 25 THEN 'İşçi tarafından işverenin ahlak ve iyiniyet kurallarına uyulmadığı için fesih'
WHEN 26 THEN 'Devamsızlık Nedeni ile fesih'
WHEN 27 THEN 'İşveren tarafından zorunlu nedenlerle ve tutukluluk sebebi ile fesih'
WHEN 28 THEN 'İşveren tarafından sağlık sebebi ile fesih'
WHEN 29 THEN 'İşveren tarafından işçinin ahlak be iyiniyet kurallarına uymama sebebi ile fesih'

end as İşten_Çıkış_Nedeni

FROM LH_002_PERSON P WITH(NOLOCK)
LEFT OUTER JOIN LH_002_FAMILY EVN WITH(NOLOCK) ON EVN.PERREF=P.LREF
LEFT OUTER JOIN LH_002_CONTACT ADRES WITH(NOLOCK) ON EVN.LREF = ADRES.CARDREF AND ADRES.TYP='1' AND ADRES.LNNR = '1'
LEFT OUTER JOIN LH_002_CONTACT  GSM WITH(NOLOCK) ON EVN.LREF = GSM.CARDREF AND GSM.TYP='3' AND GSM.LNNR = '1'
LEFT OUTER JOIN LH_002_CONTACT  TEL WITH(NOLOCK) ON EVN.LREF = TEL.CARDREF AND TEL.TYP='2' AND TEL.LNNR = '1'
LEFT OUTER JOIN LH_002_CONTACT  ARA WITH(NOLOCK) ON EVN.LREF = ARA.CARDREF AND ARA.TYP='4' AND ARA.LNNR = '1'

LEFT OUTER JOIN LH_002_PERIDINF B WITH(NOLOCK) ON B.LREF=EVN.IDREF
LEFT OUTER JOIN LH_002_LAWCHG L WITH(NOLOCK) ON P.LREF = L.PERREF AND L.EXP='Isten çikis islemi'
LEFT OUTER JOIN LH_002_LAWCHG LAW WITH(NOLOCK) ON P.LREF = LAW.PERREF
LEFT OUTER JOIN LH_002_PERFIN PER WITH(NOLOCK) ON P.LREF = PER.PERREF
LEFT JOIN L_CAPIDIV WITH(NOLOCK) ON P.LOCNR = L_CAPIDIV.NR   AND P.FIRMNR = L_CAPIDIV.FIRMNR
LEFT JOIN L_CAPIDEPT WITH(NOLOCK) ON P.FIRMNR = L_CAPIDEPT.FIRMNR AND P.DEPTNR = L_CAPIDEPT.NR
LEFT JOIN L_CAPIUNIT CAP WITH(NOLOCK) ON P.UNITNR=CAP.NR AND P.FIRMNR=CAP.FIRMNR
LEFT JOIN LH_002_QUALFDEF WITH(NOLOCK) ON P.TITLE = LH_002_QUALFDEF.CODE AND LH_002_QUALFDEF.TYP = '9'

WHERE EVN.RELATION=0 
AND
LAW.ENDDATE IS NULL AND P.FIRMNR<>'6'
GROUP BY ARA.EXP1,ARA.EXP2,GSM.EXP1,GSM.EXP2,ADRES.EXP1,ADRES.EXP2,LH_002_QUALFDEF.NAME,PER.WAGE_WAGE,P.CODE,P.NAME,P.SURNAME,
B.BLOODGROUP,P.TYP,EVN.IDREF ,P.TTFNO ,  P.yasak, B.STATUS, B.DRIVINGCLASS, B.MILTSTATUS,  L_CAPIDIV.NAME,P.FIRMNR,TEL.EXP1,
TEL.EXP2,P.DEPTNR ,L_CAPIDEPT.NAME ,B.CITY ,P.INDATE,P.OUTDATE  ,LAW.SSKSTATUS  ,P.EDUCATION ,P.BIRTHDATE ,B.BIRTHPLACE,
PER.INSSTARTDATE,CAP.NAME ,LAW.REPSTATS16,L.REPSTATS16,LAW.EXP



[Recete]

Faydası olur mu bilmem ama benim yazdığım bir sorgu var umarım işine yarar.

Elinize sağlık. Paylaştığınız için teşekkür ederim.

Sorguda yasak olan yerler var. Burada hata verdi.

Sorguyu kullanacak kişiler "s  e  x" olarak değiştirmelidir.

[Recete]

Bu da bizden olsun.

Otomatik tazminat hesaplayan bir dosya...

Düzeltme gereklidir.

Eğer düzgün çalıştırabilirseniz tekrar geri paylaşırsanız sevinirim.

Not: Dosya uzantısını xlsm yapınız.