📄 Tam SQL Kodu (Referans)
Analiz eklerken buradan kod parçalarını kopyalayabilirsiniz
CREATE VIEW dbo.GUNLUK_SATIS_ISLEMLERI_RAPORU
AS
SELECT *
FROM (/*--------------------------------------------------------------------*/ (SELECT /* Temel alanlar*/ ISLEM_TARIH, IRSALIYE_NO, FKODU, FUNVANI, MALZEME_KODU, MALZEME, MARKA, MIKTAR, /* Liste fiyati ve fiyatlar*/ ISNULL(LISTE_FIYATI, 0)
AS LISTE_FIYATI, FIYAT, TOPLAM, INDIRIM, /* KDV'ye göre fis tipi seçimi*/ CASE WHEN KDV = 0 THEN 'BEYAZ FIS' ELSE 'FATURA' END AS KDV,
/* Toplam tutari miktara bölerek satir bazinda birim tutar hesaplama*/ TOPLAM_TUTAR / MIKTAR AS TOPLAM_TUTAR,
/* Hesaplanan fiyat (null ise 0 dönülüyor)*/ ISNULL(HESAPLANAN, 0) AS HESAPLANAN,
/* Fark hesaplamasi (5 TL’nin altindaki farki 0, üstündekini mutlak degeriyle göster)*/ ISNULL(CASE WHEN ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR),
2)) < 5 THEN 0 ELSE ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) END, 0) AS FARK,
/* Yüzde fark hesaplamasi (HESAPLANAN sifir veya null degilse)*/ ROUND((CASE WHEN QQ.[FIYAT] IS NULL OR
QQ.[FIYAT] = 0 THEN 0 ELSE (ISNULL(CASE WHEN ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR))
< 5 THEN 0 ELSE ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR)) END, 0) / (QQ.[HESAPLANAN])) END) * 100, 0) AS YUZDE,
/* Durum kolonu (FIYAT KARTI YOK / INDIRIMLI / YÜKSEK FIYAT / bos)*/ CASE WHEN HESAPLANAN IS NULL
THEN 'FIYAT KARTI YOK' ELSE CASE WHEN ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) >= 0 AND
ABS(ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)) < 5 THEN '' WHEN ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2)
> 5 THEN 'INDIRIMLI' ELSE 'YÜKSEK FIYAT' END END AS DURUM, /* Iskonto kolonlari*/ ISNULL(CARI_ISKONTOSU, 0) AS CARI_ISKONTOSU,
ISNULL(MARKA_ISKONTOSU, 0) AS MARKA_ISKONTOSU, ISNULL(FIS_ISKONTOSU1, 0) AS FIS_ISKONTOSU1, ISNULL(FIS_ISKONTOSU2, 0) AS FIS_ISKONTOSU2,
ISNULL(PESIN_ISKONTO1, 0) AS PESIN_ISKONTO1, ISNULL(PESIN_ISKONTO2, 0) AS PESIN_ISKONTO2, ISNULL(TUM_ISK, 0) AS TUM_ISK, SPECODE,
/* (Örnek logic: KDV=0, Yüzde=10 ve hesaplanan fark > 5 ise 0, aksi halde 1)*/ CASE WHEN KDV = 0 AND ROUND((CASE WHEN QQ.[FIYAT] IS NULL OR
QQ.[FIYAT] = 0 THEN 0 ELSE (ISNULL(CASE WHEN ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR))
< 5 THEN 0 ELSE ABS(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR)) END, 0) / QQ.[HESAPLANAN]) END) * 100, 0) = 10 AND
ROUND(QQ.[HESAPLANAN] - (QQ.[TOPLAM_TUTAR] / QQ.MIKTAR), 2) > 5 THEN 0 ELSE 1 END AS GOSTER
FROM (/*--------------------------------------------------------------------*/ SELECT STF.DATE_ AS ISLEM_TARIH, STF.FICHENO AS IRSALIYE_NO, CC.CODE AS FKODU,
CC.DEFINITION_ AS FUNVANI, ITM.CODE AS MALZEME_KODU, ITM.NAME AS MALZEME, ITM.SPECODE4 AS MARKA, STL.AMOUNT AS MIKTAR,
STL.PRICE AS FIYAT, STL.TOTAL AS TOPLAM, STL.DISTDISC AS INDIRIM, STL.VATAMNT AS KDV, STL.VATMATRAH AS TOPLAM_TUTAR,
/* HESAPLANAN fiyatin belirlenmesi*/ ROUND(CASE WHEN ITM.SPECODE4 LIKE '%-T' THEN /*----------------------------------------------------------------------------*/ CASE WHEN STL.VATAMNT
> 0 THEN (PRC.PRICE * CASE WHEN PRC.CURRENCY = 1 THEN
(SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_) ELSE 1 END)
* 0.70 /* => %30 indirim*/ ELSE (PRC.PRICE * CASE WHEN PRC.CURRENCY = 1 THEN
(SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_) ELSE 1 END)
* 0.70 * 1.2 /* => %30 indirim*/ END ELSE /*----------------------------------------------------------------------------*/ CASE WHEN STL.VATAMNT > 0 THEN /*-----------------------------------------------------*/ ((PRC.PRICE
* CASE WHEN PRC.CURRENCY = 1 THEN
(SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_) ELSE 1 END) /* Cari iskonto + Pesin/Fis iskontolar + TUM*/ * (1 - (CC.DISCRATE / 100))
* (1 - (ISNULL(CAST(XT.PESIN AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.PESIN2 AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.FIS AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS2 AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.TUM AS FLOAT), 0) / 100)))
ELSE /*-----------------------------------------------------*/ (((PRC.PRICE * CASE WHEN PRC.CURRENCY = 1 THEN
(SELECT TOP (1) RATES1
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_) ELSE 1 END) * 1.2/* KDV Yoksa 1.2 Çarpani (örnek: eskiden SART kosullarinda kullaniliyordu)*/ )
* (1 - (CC.DISCRATE / 100)) * (1 - (ISNULL(CAST(XT.PESIN AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.PESIN2 AS FLOAT), 0) / 100))
* (1 - (ISNULL(CAST(XT.FIS AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.FIS2 AS FLOAT), 0) / 100)) * (1 - (ISNULL(CAST(XT.TUM AS FLOAT), 0) / 100)))
END END, 2) AS HESAPLANAN, /* Liste fiyati (Kuru bilgisine göre çevirim yapiliyor)*/ PRC.PRICE * (CASE WHEN PRC.CURRENCY = 1 THEN
(SELECT TOP (1) [RATES1]
FROM [UNIVERSALDB].[dbo].[L_DAILYEXCHANGES]
WHERE CRTYPE = 1 AND EDATE = STF.DATE_) ELSE 1 END) AS LISTE_FIYATI, /* Iskonto oranlari*/ CC.DISCRATE AS CARI_ISKONTOSU,
ISK.ISKONTO AS MARKA_ISKONTOSU, XT.FIS AS FIS_ISKONTOSU1, XT.FIS2 AS FIS_ISKONTOSU2, XT.PESIN AS PESIN_ISKONTO1,
XT.PESIN2 AS PESIN_ISKONTO2, XT.TUM AS TUM_ISK, /* Specode*/ STL.SPECODE
FROM dbo.LG_011_08_STLINE AS STL LEFT OUTER JOIN
dbo.LG_011_08_STFICHE AS STF ON STF.LOGICALREF = STL.STFICHEREF LEFT OUTER JOIN
dbo.LG_011_CLCARD AS CC ON CC.LOGICALREF = STL.CLIENTREF LEFT OUTER JOIN
dbo.LG_XT1015_011 AS XT ON XT.PARLOGREF = STL.CLIENTREF LEFT OUTER JOIN
dbo.LG_011_ITEMS AS ITM ON ITM.LOGICALREF = STL.STOCKREF LEFT OUTER JOIN
dbo.ISKONTO_MARKA AS ISK ON ISK.LOGICALREF = STL.STOCKREF LEFT OUTER JOIN
dbo.LG_011_PRCLIST AS PRC ON PRC.CARDREF = STL.STOCKREF AND PRC.PTYPE = 2
WHERE STL.TRCODE = 8 /* Irsaliye tipi*/ AND STL.CANCELLED = 0 /* Iptal edilmemis satirlar*/ AND STL.LINETYPE = 0 /* Normal satir*/ AND ITM.CODE <> 'A01-NAK')
AS QQ
/* 5 TL'den fazla fark olacak.*/ WHERE (HESAPLANAN IS NULL) OR
(ABS(HESAPLANAN - TOPLAM_TUTAR / MIKTAR) > 5))/*--------------------------------------------------------------------*/ ) TT
/* Son filtre: GOSTER=1 olan kayitlari getir.*/ WHERE TT.GOSTER = 1 AND TT.SPECODE = '';
➕ Yeni Kod Analizi Ekle
📋 Mevcut Analizler (0)
Henüz kod analizi eklenmemiş.
Yukarıdaki formu kullanarak ilk analizi ekleyin.