Logo Destek

Database => SQL => Konuyu başlatan: halisbatmaz - 21.12.2011, 15:09

Başlık: BORÇ -ALACAK ORTALAMA VADE
Gönderen: halisbatmaz - 21.12.2011, 15:09

cast(SUM(TOTAL*cast(DATE_ as int) )/ (SUM(TOTAL))as datetime) FROM LG_008_04_PAYTRANS
bu sorguyu  buraya nasıl yerleştirebilirim ayrıca KODA GÖRE ARAMAYI NASIL ARTIABİLİRİM or like 'A.N%' Yaptım olmadı. Yardımcı olursan çok makbule geçer.
Select   CLCARD.CODE AS KODU,CLCARD.CITY AS ŞEHİR,   CLCARD.DEFINITION_ AS ÜNVANI,CLCARD.SPECODE AS PLASİYER,      CASE WHEN Sum(Case when SIGN=0 then 1 ELSE -1 END*CLFLINE.AMOUNT)>=0 THEN Sum(Case when SIGN=0 then 1 ELSE -1 END*CLFLINE.AMOUNT) ELSE 0 END AS BAKIYE_BORC,  CASE WHEN Sum(Case when SIGN=0 then 1 ELSE -1 END*CLFLINE.AMOUNT)<0 THEN Sum(Case when SIGN=0 then 1 ELSE -1 END*CLFLINE.AMOUNT) ELSE 0 END AS BAKIYE_ALACAK  FROM LG_008_04_CLFLINE CLFLINE   INNER JOIN LG_008_CLCARD CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED=0  GROUP BY CLCARD.CODE, CLCARD.CITY, CLCARD.SPECODE, CLCARD.DEFINITION_  HAVING (((CLCARD.CODE) Like 'A.V%'))   ORDER BY CLCARD.DEFINITION_   

Başlık: Ynt: BORÇ -ALACAK ORTALAMA VADE
Gönderen: mymecaz - 21.12.2011, 16:01

SELECT   
(select top(1) cast(SUM(TOTAL*cast(DATE_ as int) )/ (SUM(TOTAL))as datetime) FROM LG_008_04_PAYTRANS) AS ISTENILENALAN,
 CLCARD.CODE AS KODU, CLCARD.CITY AS ŞEHİR, CLCARD.DEFINITION_ AS ÜNVANI, CLCARD.SPECODE AS PLASİYER,
                      CASE WHEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT) >= 0 THEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT)
                       ELSE 0 END AS BAKIYE_BORC, CASE WHEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT)
                      < 0 THEN SUM(CASE WHEN SIGN = 0 THEN 1 ELSE - 1 END * CLFLINE.AMOUNT) ELSE 0 END AS BAKIYE_ALACAK
FROM         LG_008_04_CLFLINE AS CLFLINE INNER JOIN
                      LG_008_CLCARD AS CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF AND CLFLINE.CANCELLED = 0
GROUP BY CLCARD.CODE, CLCARD.CITY, CLCARD.SPECODE, CLCARD.DEFINITION_
HAVING      (CLCARD.CODE LIKE 'A.N%')
ORDER BY ÜNVANI

Başlık: Ynt: BORÇ -ALACAK ORTALAMA VADE
Gönderen: muratbkk - 26.02.2022, 01:19

Logodaki Detaylı ortalama Vade Raporu içerisinde Borç Ve Alacak için Ortalama Vade sorguları aşağıdaki gibidir.
(Kapanmamışlar için)

select cast(SUM(TOTAL*cast(DATE_ as int) )/ ((SUM(TOTAL)-SUM(PAID)))as datetime) FROM LG_200_06_PAYTRANS where SIGN=0 AND CARDREF = 23929 AND (TOTAL-PAID)> 0.01
select cast(SUM(TOTAL*cast(DATE_ as int) )/ ((SUM(TOTAL)-SUM(PAID)))as datetime) FROM LG_200_06_PAYTRANS where SIGN=1 AND CARDREF = 23929 AND (TOTAL-PAID)> 0.01

CARDREF  = istemiş olduğunuz Carinin LOGICALREF

Tüm hareketlere bakılmak istenirse sorgunun sonundaki alanı silebilirsiniz.