Logo ERP SQL ile Excel Raporu Oluşturma

SELECT
    YEAR(I.DATE_) AS Yil,
    MONTH(I.DATE_) AS Ay,
    COUNT(*) AS FaturaAdedi,
    SUM(I.GROSSTOTAL) AS BrutCiro,
    SUM(I.TOTALVAT) AS ToplamKDV,
    SUM(I.NETTOTAL) AS NetCiro
FROM LG_001_01_INVOICE I
WHERE I.TRCODE IN (2, 3)
  AND I.CANCELLED = 0
GROUP BY YEAR(I.DATE_), MONTH(I.DATE_)
ORDER BY Yil, Ay;

SELECT
    S.ITEMCODE,
    S.ITEMNAME,
    SUM(CASE WHEN S.IOCODE IN (1,2) THEN S.AMOUNT ELSE 0 END) AS ToplamGiris,
    SUM(CASE WHEN S.IOCODE IN (3,4) THEN S.AMOUNT ELSE 0 END) AS ToplamCikis
FROM LG_001_01_STLINE S
WHERE S.DATE_ BETWEEN '2026-01-01' AND '2026-03-31'
GROUP BY S.ITEMCODE, S.ITEMNAME
ORDER BY S.ITEMCODE;

SELECT
    C.CODE,
    C.DEFINITION_ AS CariAdi,
    SUM(CASE WHEN DATEDIFF(DAY, T.DATE_, GETDATE()) <= 30
        THEN T.AMOUNT ELSE 0 END) AS Vade0_30,
    SUM(CASE WHEN DATEDIFF(DAY, T.DATE_, GETDATE()) BETWEEN 31 AND 60
        THEN T.AMOUNT ELSE 0 END) AS Vade31_60,
    SUM(CASE WHEN DATEDIFF(DAY, T.DATE_, GETDATE()) > 60
        THEN T.AMOUNT ELSE 0 END) AS Vade60Plus
FROM LG_001_01_CLFLINE T
JOIN LG_001_CLCARD C ON C.LOGICALREF = T.CLIENTREF
WHERE T.CANCELLED = 0
GROUP BY C.CODE, C.DEFINITION_
ORDER BY C.CODE;

SELECT TOP 20
    S.ITEMCODE,
    S.ITEMNAME,
    SUM(S.AMOUNT) AS ToplamMiktar,
    SUM(S.LINENET) AS ToplamCiro
FROM LG_001_01_STLINE S
JOIN LG_001_01_INVOICE I ON I.LOGICALREF = S.INVOICEREF
WHERE I.TRCODE IN (2, 3)
  AND I.CANCELLED = 0
GROUP BY S.ITEMCODE, S.ITEMNAME
ORDER BY ToplamCiro DESC;

SELECT
    K.CODE AS KasaKodu,
    K.DEFINITION_ AS KasaAdi,
    SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE 0 END) AS ToplamGiris,
    SUM(CASE WHEN L.SIGN = 1 THEN L.AMOUNT ELSE 0 END) AS ToplamCikis,
    SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END) AS NetBakiye
FROM LG_001_01_KSLINES L
JOIN LG_001_KSCARD K ON K.LOGICALREF = L.CARDREF
WHERE CAST(L.DATE_ AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY K.CODE, K.DEFINITION_;