Minerva Plantum SQL Sorguları

SELECT s.STK_KOD AS STOK_KODU, s.STK_ADI AS STOK_ADI, s.STK_BRM AS BIRIM, s.STK_BAK AS MEVCUT_BAKIYE, s.STK_GRP AS GRUP, s.STK_LOK AS LOKASYON FROM STK_STOK s WHERE s.STK_BAK > 0 AND s.STK_AKT = 1 ORDER BY s.STK_BAK DESC

SELECT h.STK_KOD AS STOK_KODU, s.STK_ADI AS STOK_ADI, SUM(CASE WHEN h.HRK_TIP = 'G' THEN h.HRK_MKT END) AS TOPLAM_GIRIS, SUM(CASE WHEN h.HRK_TIP = 'C' THEN h.HRK_MKT END) AS TOPLAM_CIKIS, SUM(CASE WHEN h.HRK_TIP = 'G' THEN h.HRK_MKT ELSE -h.HRK_MKT END) AS NET_HAREKET FROM STK_HRKT h JOIN STK_STOK s ON h.STK_KOD = s.STK_KOD WHERE h.HRK_TAR >= DATEADD(month, -1, GETDATE()) GROUP BY h.STK_KOD, s.STK_ADI ORDER BY NET_HAREKET DESC

SELECT s.STK_KOD AS STOK_KODU, s.STK_ADI AS STOK_ADI, s.STK_BAK AS MEVCUT, s.STK_MIN AS MINIMUM_SEVIYE, s.STK_BAK - s.STK_MIN AS FARK, s.STK_LOK AS DEPO FROM STK_STOK s WHERE s.STK_BAK < s.STK_MIN AND s.STK_AKT = 1 AND s.STK_MIN > 0 ORDER BY FARK ASC

SELECT s.STK_KOD, s.STK_ADI, SUM(h.HRK_MKT) AS TOPLAM_CIKIS, AVG(s.STK_BAK) AS ORT_STOK, ROUND( SUM(h.HRK_MKT) / NULLIF(AVG(s.STK_BAK), 0), 2 ) AS DEVIR_HIZI FROM STK_STOK s JOIN STK_HRKT h ON s.STK_KOD = h.STK_KOD WHERE h.HRK_TIP = 'C' AND h.HRK_TAR >= DATEADD(month, -3, GETDATE()) GROUP BY s.STK_KOD, s.STK_ADI HAVING AVG(s.STK_BAK) > 0 ORDER BY DEVIR_HIZI DESC

SELECT c.CRI_KOD AS CARI_KOD, c.CRI_ADI AS MUSTERI_ADI, COUNT(f.FAT_NO) AS FATURA_ADET, SUM(f.FAT_NET) AS NET_TUTAR, SUM(f.FAT_KDV) AS KDV_TUTARI, SUM(f.FAT_TOP) AS GENEL_TOPLAM FROM SAT_FATURA f JOIN CRI_CARI c ON f.CRI_KOD = c.CRI_KOD WHERE f.FAT_TAR >= DATEADD(month, -3, GETDATE()) AND f.FAT_TIP = 'S' AND f.FAT_IPT = 0 GROUP BY c.CRI_KOD, c.CRI_ADI ORDER BY GENEL_TOPLAM DESC

SELECT sp.SIP_NO AS SIPARIS_NO, c.CRI_ADI AS MUSTERI, sp.SIP_TAR AS SIPARIS_TARIHI, sp.SIP_TES AS TESLIM_TARIHI, sp.SIP_TOT AS SIPARIS_TUTARI, sp.SIP_KAL AS KALAN_MIKTAR, DATEDIFF(day, sp.SIP_TES, GETDATE()) AS GECIKME_GUN FROM SAT_SIPARIS sp JOIN CRI_CARI c ON sp.CRI_KOD = c.CRI_KOD WHERE sp.SIP_DRM = 'A' AND sp.SIP_KAL > 0 ORDER BY GECIKME_GUN DESC

SELECT YEAR(f.FAT_TAR) AS YIL, MONTH(f.FAT_TAR) AS AY, COUNT(*) AS FATURA_SAYISI, SUM(f.FAT_NET) AS NET_SATIS, SUM(f.FAT_TOP) AS BRUT_SATIS, AVG(f.FAT_NET) AS ORT_FATURA FROM SAT_FATURA f WHERE f.FAT_TIP = 'S' AND f.FAT_IPT = 0 AND f.FAT_TAR >= DATEADD(year, -1, GETDATE()) GROUP BY YEAR(f.FAT_TAR), MONTH(f.FAT_TAR) ORDER BY YIL DESC, AY DESC

SELECT sv.SVK_NO AS SEVKIYAT_NO, c.CRI_ADI AS MUSTERI, sv.SVK_TAR AS SEVKIYAT_TARIHI, sv.SVK_ADR AS TESLIMAT_ADRESI, sv.SVK_DRM AS DURUM, sv.SVK_ARC AS ARAC_PLAKA, sv.SVK_SRC AS SURUC FROM LOJ_SEVKIYAT sv JOIN CRI_CARI c ON sv.CRI_KOD = c.CRI_KOD WHERE sv.SVK_TAR >= DATEADD(day, -7, GETDATE()) ORDER BY sv.SVK_TAR DESC

SELECT sv.SVK_ARC AS ARAC_PLAKA, COUNT(*) AS SEFER_SAYISI, SUM(sv.SVK_KM) AS TOPLAM_KM, SUM(sv.SVK_MLY) AS TOPLAM_MALIYET, ROUND( SUM(sv.SVK_MLY) / NULLIF(SUM(sv.SVK_KM), 0), 2 ) AS KM_BASINA_MALIYET FROM LOJ_SEVKIYAT sv WHERE sv.SVK_TAR >= DATEADD(month, -1, GETDATE()) AND sv.SVK_DRM = 'T' GROUP BY sv.SVK_ARC ORDER BY TOPLAM_MALIYET DESC

SELECT c.CRI_KOD AS CARI_KOD, c.CRI_ADI AS CARI_ADI, c.CRI_TIP AS TIP, SUM(CASE WHEN h.MUH_TIP = 'B' THEN h.MUH_TUT END) AS TOPLAM_BORC, SUM(CASE WHEN h.MUH_TIP = 'A' THEN h.MUH_TUT END) AS TOPLAM_ALACAK, SUM(CASE WHEN h.MUH_TIP = 'B' THEN h.MUH_TUT ELSE -h.MUH_TUT END) AS BAKIYE FROM CRI_CARI c JOIN MUH_HRKT h ON c.CRI_KOD = h.CRI_KOD WHERE h.MUH_TAR <= GETDATE() AND c.CRI_AKT = 1 GROUP BY c.CRI_KOD, c.CRI_ADI, c.CRI_TIP HAVING SUM(CASE WHEN h.MUH_TIP = 'B' THEN h.MUH_TUT ELSE -h.MUH_TUT END) <> 0 ORDER BY ABS(BAKIYE) DESC

SELECT c.CRI_ADI AS MUSTERI, f.FAT_NO AS FATURA_NO, f.FAT_TAR AS FATURA_TARIHI, f.FAT_VDE AS VADE_TARIHI, f.FAT_BAK AS KALAN_TUTAR, DATEDIFF(day, f.FAT_VDE, GETDATE()) AS GECIKME_GUN FROM SAT_FATURA f JOIN CRI_CARI c ON f.CRI_KOD = c.CRI_KOD WHERE f.FAT_BAK > 0 AND f.FAT_VDE < GETDATE() AND f.FAT_TIP = 'S' AND f.FAT_IPT = 0 ORDER BY GECIKME_GUN DESC

SELECT MONTH(f.FAT_TAR) AS AY, YEAR(f.FAT_TAR) AS YIL, f.FAT_TIP AS ISLEM_TIPI, SUM(f.FAT_NET) AS MATRAH, SUM(f.FAT_KDV) AS KDV_TUTARI, SUM(f.FAT_TOP) AS TOPLAM, COUNT(*) AS BELGE_SAYISI FROM SAT_FATURA f WHERE f.FAT_IPT = 0 AND f.FAT_TAR BETWEEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AND GETDATE() GROUP BY MONTH(f.FAT_TAR), YEAR(f.FAT_TAR), f.FAT_TIP ORDER BY YIL, AY, f.FAT_TIP

SELECT p.PRS_KOD AS PERSONEL_NO, p.PRS_ADI AS AD_SOYAD, p.PRS_DEP AS DEPARTMAN, p.PRS_POZ AS POZISYON, p.PRS_GRS AS GIRIS_TARIHI, DATEDIFF(year, p.PRS_GRS, GETDATE()) AS KDEM_YIL FROM IK_PERSONEL p WHERE p.PRS_DRM = 'A' ORDER BY p.PRS_DEP, p.PRS_ADI

SELECT p.PRS_DEP AS DEPARTMAN, COUNT(*) AS PERSONEL_SAYISI, SUM(CASE WHEN p.PRS_CIN = 'E' THEN 1 END) AS ERKEK, SUM(CASE WHEN p.PRS_CIN = 'K' THEN 1 END) AS KADIN, ROUND(AVG( DATEDIFF(year, p.PRS_GRS, GETDATE()) ), 1) AS ORT_KDEM FROM IK_PERSONEL p WHERE p.PRS_DRM = 'A' GROUP BY p.PRS_DEP ORDER BY PERSONEL_SAYISI DESC

SELECT p.PRS_ADI AS PERSONEL, p.PRS_DEP AS DEPARTMAN, i.IZN_HKK AS HAKKEDILEN_GUN, i.IZN_KUL AS KULLANILAN_GUN, i.IZN_HKK - i.IZN_KUL AS KALAN_IZIN, i.IZN_YIL AS IZIN_YILI FROM IK_PERSONEL p JOIN IK_IZIN i ON p.PRS_KOD = i.PRS_KOD WHERE p.PRS_DRM = 'A' AND i.IZN_YIL = YEAR(GETDATE()) ORDER BY KALAN_IZIN DESC