- Katılım
- 22 Ağustos 2023
- Mesajlar
- 7
- Excel Vers. ve Dili
- TÜRK
merhaba;
banka hesapları bazında vadesi gelmeyen çek senet alacak borç toplam rapor için yardımcı olabilir misiniz?
mevcut sorguya ekleme yapar mısınız?
-- Ana Sorgu
SELECT
CASE
WHEN HESAP.CARDTYPE = 1 THEN 'TİCARİ HESAP'
WHEN HESAP.CARDTYPE = 2 THEN 'KREDİ HESABI'
WHEN HESAP.CARDTYPE = 3 THEN 'DÖVİZLİ TİCARİ HESABI'
WHEN HESAP.CARDTYPE = 4 THEN 'DÖVİZLİ KREDİ HESABI'
WHEN HESAP.CARDTYPE = 5 THEN 'KREDİ KARTI HESABI'
ELSE 'DİĞER'
END AS HESAP_TÜRÜ,
BANKA.DEFINITION_ AS 'BANKA ADI',
HESAP.DEFINITION_ AS 'HESAP ADI',
BANKA.CODE AS 'BANKA KODU',
HESAP.CODE AS 'HESAP KODU',
HESAP.CREDITCARDLIMIT AS 'TOPLAM LİMİT',
HESAP.WTHCLTRLLIMIT AS 'KREDİ HESAP LİMİTİ',
ISNULL(
ROUND(
SUM(
CASE
WHEN H.SIGN = 1 THEN (H.AMOUNT * -1) / NULLIF(
CASE
WHEN HESAP.CURRENCY IN (0, 160) THEN 1
ELSE H.TRRATE
END,
0
)
WHEN H.SIGN = 0 THEN H.AMOUNT / NULLIF(
CASE
WHEN HESAP.CURRENCY IN (0, 160) THEN 1
ELSE H.TRRATE
END,
0
)
END
),
2
),
0
) AS 'BAKİYE'
FROM LG_620_BNCARD AS BANKA
INNER JOIN LG_620_BANKACC AS HESAP ON HESAP.BANKREF = BANKA.LOGICALREF
INNER JOIN LG_620_01_BNFLINE AS H ON H.BANKREF = BANKA.LOGICALREF AND H.BNACCREF = HESAP.LOGICALREF
WHERE BANKA.ACTIVE = 0
AND HESAP.ACTIVE = 0
AND H.TRANSTYPE NOT IN (3)
GROUP BY
HESAP.CARDTYPE,
BANKA.DEFINITION_,
HESAP.DEFINITION_,
BANKA.CODE,
HESAP.CODE,
HESAP.CREDITCARDLIMIT,
HESAP.WTHCLTRLLIMIT,
HESAP.BATCHNUM,
HESAP.CHECKMARGIN
ORDER BY BANKA.DEFINITION_ ASC;
banka hesapları bazında vadesi gelmeyen çek senet alacak borç toplam rapor için yardımcı olabilir misiniz?
mevcut sorguya ekleme yapar mısınız?
-- Ana Sorgu
SELECT
CASE
WHEN HESAP.CARDTYPE = 1 THEN 'TİCARİ HESAP'
WHEN HESAP.CARDTYPE = 2 THEN 'KREDİ HESABI'
WHEN HESAP.CARDTYPE = 3 THEN 'DÖVİZLİ TİCARİ HESABI'
WHEN HESAP.CARDTYPE = 4 THEN 'DÖVİZLİ KREDİ HESABI'
WHEN HESAP.CARDTYPE = 5 THEN 'KREDİ KARTI HESABI'
ELSE 'DİĞER'
END AS HESAP_TÜRÜ,
BANKA.DEFINITION_ AS 'BANKA ADI',
HESAP.DEFINITION_ AS 'HESAP ADI',
BANKA.CODE AS 'BANKA KODU',
HESAP.CODE AS 'HESAP KODU',
HESAP.CREDITCARDLIMIT AS 'TOPLAM LİMİT',
HESAP.WTHCLTRLLIMIT AS 'KREDİ HESAP LİMİTİ',
ISNULL(
ROUND(
SUM(
CASE
WHEN H.SIGN = 1 THEN (H.AMOUNT * -1) / NULLIF(
CASE
WHEN HESAP.CURRENCY IN (0, 160) THEN 1
ELSE H.TRRATE
END,
0
)
WHEN H.SIGN = 0 THEN H.AMOUNT / NULLIF(
CASE
WHEN HESAP.CURRENCY IN (0, 160) THEN 1
ELSE H.TRRATE
END,
0
)
END
),
2
),
0
) AS 'BAKİYE'
FROM LG_620_BNCARD AS BANKA
INNER JOIN LG_620_BANKACC AS HESAP ON HESAP.BANKREF = BANKA.LOGICALREF
INNER JOIN LG_620_01_BNFLINE AS H ON H.BANKREF = BANKA.LOGICALREF AND H.BNACCREF = HESAP.LOGICALREF
WHERE BANKA.ACTIVE = 0
AND HESAP.ACTIVE = 0
AND H.TRANSTYPE NOT IN (3)
GROUP BY
HESAP.CARDTYPE,
BANKA.DEFINITION_,
HESAP.DEFINITION_,
BANKA.CODE,
HESAP.CODE,
HESAP.CREDITCARDLIMIT,
HESAP.WTHCLTRLLIMIT,
HESAP.BATCHNUM,
HESAP.CHECKMARGIN
ORDER BY BANKA.DEFINITION_ ASC;