29-04-2015, 04:43
Sn @MSC;
brans ve unvanlari bos olanlanlara isaretleme ile personel saydirilmistir.
SELECT personel.personel_kadro_kurum, personel.personel_sinif, IIf(IsNull([personel_akademik_unvan]),"____BOS____",[2_unvan]![unvan_adi]) AS Unvan, IIf(IsNull([personel_brans]),"____BOS____",[3_brans]![brans_adi]) AS Brans, Count(personel.personel_id) AS PersonelSayisi, Sum(IIf([izin_baslayis]>[SysTarih] And [izin_bitis]<[SysTarih] Or IsNull([izin]![personel_id]),1,0)) AS Aktif, Sum(IIf([izin_baslayis]<=[SysTarih] And [izin_bitis]>=[SysTarih],1,0)) AS Izinde
FROM (3_brans RIGHT JOIN (2_unvan RIGHT JOIN personel ON [2_unvan].unvan_id = personel.personel_unvan) ON [3_brans].brans_id = personel.personel_brans) LEFT JOIN izin ON personel.personel_id = izin.personel_id
GROUP BY personel.personel_kadro_kurum, personel.personel_sinif, IIf(IsNull([personel_akademik_unvan]),"____BOS____",[2_unvan]![unvan_adi]), IIf(IsNull([personel_brans]),"____BOS____",[3_brans]![brans_adi]);
brans ve unvanlari bos olanlanlara isaretleme ile personel saydirilmistir.
SELECT personel.personel_kadro_kurum, personel.personel_sinif, IIf(IsNull([personel_akademik_unvan]),"____BOS____",[2_unvan]![unvan_adi]) AS Unvan, IIf(IsNull([personel_brans]),"____BOS____",[3_brans]![brans_adi]) AS Brans, Count(personel.personel_id) AS PersonelSayisi, Sum(IIf([izin_baslayis]>[SysTarih] And [izin_bitis]<[SysTarih] Or IsNull([izin]![personel_id]),1,0)) AS Aktif, Sum(IIf([izin_baslayis]<=[SysTarih] And [izin_bitis]>=[SysTarih],1,0)) AS Izinde
FROM (3_brans RIGHT JOIN (2_unvan RIGHT JOIN personel ON [2_unvan].unvan_id = personel.personel_unvan) ON [3_brans].brans_id = personel.personel_brans) LEFT JOIN izin ON personel.personel_id = izin.personel_id
GROUP BY personel.personel_kadro_kurum, personel.personel_sinif, IIf(IsNull([personel_akademik_unvan]),"____BOS____",[2_unvan]![unvan_adi]), IIf(IsNull([personel_brans]),"____BOS____",[3_brans]![brans_adi]);