Herkese selamlar,
şöyle bir sorgu hazırladım sorgu isteğime cevap veriyor ancak Çek Risk Tutarında şöyle bir sorun çıkıyor, Vadesi geldiyse ve tahsil edilmediyse çek riskine eklemem gerekiyor ancak tahsil durumunu sorgunun neresine koymalıyım? (2 haftadır çalıştığım bir sorgu bu sorguyu bedavaya bulamazsınız
)
DECLARE @BasTarih VARCHAR(20)= '20140101', @SonTarih VARCHAR(20) = '20140227'
SELECT
CariKartAna.CODE AS [Cari Kod],
CariKartAna.DEFINITION_ AS [Cari Ad],
CariHesapHareketAna.AMOUNT AS [Açılış Bakiye],
--Devir Çekleri
(SELECT ISNULL(SUM(CariKart.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CariKart ON CekSenetHareket.CSREF = CariKart.LOGICALREF
WHERE (CekSenetHareket.CARDREF = (SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetHareket.DEVIR = 1))
AS [Devir Çekleri],
(ISNULL((
SELECT SUM([Toplam Bakiye])
FROM (
SELECT
SUM(Fatura.NETTOTAL) AS [Toplam Bakiye]
FROM LKSDB.dbo.LG_214_01_INVOICE AS Fatura INNER JOIN
LKSDB.dbo.LG_214_CLCARD AS CariKart0 ON Fatura.CLIENTREF = CariKart0.LOGICALREF
WHERE (Fatura.TRCODE =
AND (CariKart0.ACTIVE = 0)
GROUP BY CariKart0.CODE,Fatura.DATE_
HAVING (CariKart0.CODE = CariKartAna.CODE)
AND (Fatura.DATE_ < CONVERT(DATETIME, @BasTarih, 104) )
) AS A
),0) + CariHesapHareketAna.AMOUNT )- ISNULL((SELECT
SUM(C.AMOUNT) 'Alınan Ödemeler'
FROM LKSDB.dbo.LG_214_01_CLFLINE C
INNER JOIN LKSDB.dbo.LG_214_CLCARD CR ON C.CLIENTREF=CR.LOGICALREF
where C.TRCODE IN (31,61,62) AND C.DATE_ < CONVERT(DATE, @BasTarih,104) AND CR.CODE = CariKartAna.Code ) ,0)
AS [Başlangıç Tarihi Bakiye] ,
(ISNULL((
SELECT SUM([Toplam Bakiye])
FROM (
SELECT
SUM(Fatura.NETTOTAL) AS [Toplam Bakiye]
FROM LKSDB.dbo.LG_214_01_INVOICE AS Fatura INNER JOIN
LKSDB.dbo.LG_214_CLCARD AS CariKart0 ON Fatura.CLIENTREF = CariKart0.LOGICALREF
WHERE (Fatura.TRCODE =
AND (CariKart0.ACTIVE = 0)
GROUP BY CariKart0.CODE,Fatura.DATE_
HAVING (CariKart0.CODE = CariKartAna.CODE)
AND (Fatura.DATE_ < CONVERT(DATETIME, @SonTarih, 104) )
) AS A
),0)+ CariHesapHareketAna.AMOUNT) - ISNULL((SELECT
SUM(C.AMOUNT) 'Alınan Ödemeler'
FROM LKSDB.dbo.LG_214_01_CLFLINE C
INNER JOIN LKSDB.dbo.LG_214_CLCARD CR ON C.CLIENTREF=CR.LOGICALREF
where C.TRCODE IN (31,61,62) AND C.DATE_ < CONVERT(DATE, @SonTarih,104) AND CR.CODE = CariKartAna.Code ) ,0)
AS [Bitiş Tarihi Bakiye] ,
ISNULL((SELECT SUM(NET_SATISLAR)
FROM ( SELECT
CASE MalzemeHareket.DECPRDIFF
WHEN 0 THEN SUM(ISNULL(MalzemeHareket.VATAMNT,0) + ISNULL(MalzemeHareket.LINENET,0))
WHEN 1 THEN (SUM(ISNULL(MalzemeHareket.VATAMNT,0) + ISNULL(MalzemeHareket.LINENET,0)) + CariHesapHareketAna.AMOUNT * - 1)
ELSE -99
END AS NET_SATISLAR
FROM LKSDB.dbo.LG_214_01_INVOICE as Fatura INNER JOIN
LKSDB.dbo.LG_214_01_STLINE as MalzemeHareket ON Fatura.LOGICALREF = MalzemeHareket.INVOICEREF INNER JOIN
LKSDB.dbo.LG_214_CLCARD AS CariKart11 ON Fatura.CLIENTREF = CariKart11.LOGICALREF INNER JOIN
LKSDB.dbo.LG_214_ITEMS AS Malzeme ON MalzemeHareket.STOCKREF = Malzeme.LOGICALREF
GROUP BY CariKart11.CODE, Fatura.TRCODE, MalzemeHareket.DECPRDIFF, Fatura.DATE_
HAVING (CariKart11.CODE = CariKartAna.CODE) AND Fatura.DATE_ <= @SonTarih
)
as NS),0) AS [Net Satış]
,
CariSonBakiyeAna.DEBIT - CariSonBakiyeAna.CREDIT AS [Bu gün Bakiyesi],
--Çek Risk Tutarı
(SELECT ISNULL(SUM(CekSenetKart2.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket2
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart2 ON CekSenetHareket2.CSREF = CekSenetKart2.LOGICALREF
WHERE (CekSenetHareket2.CARDREF = (SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetKart2.CURRSTAT IN (8, 4, 1, 2))
AND (CONVERT(DATE, CekSenetKart2.DUEDATE, 104) > CONVERT(DATE,@SonTarih , 104))) AS [Çek Risk Tutarı],
--Ortalama Alacak
((ISNULL(CariHesapHareketAna.AMOUNT,0) /*Açılış Bakiyesi*/
+
(SELECT ISNULL(SUM(CekSenetKart3.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket3
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart3 ON CekSenetHareket3.CSREF = CekSenetKart3.LOGICALREF
WHERE (CekSenetHareket3.CARDREF =(SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetHareket3.DEVIR = 1))) /*Devir Çekleri*/
+
(ISNULL(CariSonBakiyeAna.DEBIT,0) - ISNULL(CariSonBakiyeAna.CREDIT,0))/*Bu gün bakiyesi*/
+
(SELECT ISNULL(SUM(CekSenetKart4.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket4
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart4 ON CekSenetHareket4.CSREF = CekSenetKart4.LOGICALREF
WHERE (CekSenetHareket4.CARDREF = (SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetKart4.CURRSTAT IN (8, 4, 1, 2))
AND (CONVERT(DATE, CekSenetKart4.DUEDATE, 104) > CONVERT(DATE, @SonTarih, 104))) /*Çek Risk Tutarı*/
) / 2 AS [Ortalama Alacak],
--Alacak Devir Hızı -- NetSatışlar / Ortalama Alacak
ROUND(
ISNULL(CariSonBakiyeAna.DEBIT,0) /*Net Satışlar*/
/
/*Açılış Bakiyesi*/
ISNULL(NULLIF((((ISNULL(CariHesapHareketAna.AMOUNT,0)
+
/*Devir Çekleri*/
(SELECT ISNULL(SUM(CekSenetKart5.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket5
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart5 ON CekSenetHareket5.CSREF = CekSenetKart5.LOGICALREF
WHERE (CekSenetHareket5.CARDREF = (SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetHareket5.DEVIR = 1)))
+
/*Bu gün bakiyesi*/
(ISNULL(CariSonBakiyeAna.DEBIT,0) - ISNULL(CariSonBakiyeAna.CREDIT,0))
+
/*Çek Risk Tutarı*/
(SELECT ISNULL(SUM(CekSenetKart6.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket6
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart6 ON CekSenetHareket6.CSREF = CekSenetKart6.LOGICALREF
WHERE (CekSenetHareket6.CARDREF =(SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetKart6.CURRSTAT IN (8, 4, 1, 2))
AND (CONVERT(DATE, CekSenetKart6.DUEDATE, 104) > CONVERT(DATE, @SonTarih, 104)))
)/ 2),0),1) ,4) AS [Alacak Devir Hızı],
CONVERT(INT,CariHesapHareketAna.DATE_ - CONVERT(DATE, @SonTarih , 104)) * - 1 + 1 AS [Rapor Gün Sayısı],
--ORTALAMA TAHSİL SÜRESİ
(CONVERT(INT, CariHesapHareketAna.DATE_ - CONVERT(DATE, @SonTarih, 104)) * - 1 + 1) --Rapor tarihinden bu güne kaç gün geçmiş /55
/
--ALACAK DEVİR HIZI
ISNULL(NULLIF(
(
ROUND(
ISNULL(CariSonBakiyeAna.DEBIT,0) /*Net Satışlar*/
/
/*Açılış Bakiyesi*/
ISNULL(NULLIF((((ISNULL(CariHesapHareketAna.AMOUNT,0)
+
/*Devir Çekleri*/
(SELECT ISNULL(SUM(CekSenetKart5.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket5
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart5 ON CekSenetHareket5.CSREF = CekSenetKart5.LOGICALREF
WHERE (CekSenetHareket5.CARDREF = (SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetHareket5.DEVIR = 1)))
+
/*Bu gün bakiyesi*/
(ISNULL(CariSonBakiyeAna.DEBIT,0) - ISNULL(CariSonBakiyeAna.CREDIT,0))
+
/*Çek Risk Tutarı*/
(SELECT ISNULL(SUM(CekSenetKart6.AMOUNT), 0) AS Expr1
FROM LKSDB.dbo.LG_214_01_CSTRANS AS CekSenetHareket6
INNER JOIN LKSDB.dbo.LG_214_01_CSCARD AS CekSenetKart6 ON CekSenetHareket6.CSREF = CekSenetKart6.LOGICALREF
WHERE (CekSenetHareket6.CARDREF =(SELECT LOGICALREF FROM LKSDB.dbo.LG_214_CLCARD
WHERE CODE = CariKartAna.CODE)) AND (CekSenetKart6.CURRSTAT IN (8, 4, 1, 2))
AND (CONVERT(DATE, CekSenetKart6.DUEDATE, 104) > CONVERT(DATE, @SonTarih, 104)))
)/ 2),0),1) ,4)
),0),1)
AS [Ortalama Tahsil Süresi]
FROM LKSDB.dbo.LG_214_CLCARD AS CariKartAna
INNER JOIN LKSDB.dbo.LG_214_01_CLFLINE AS CariHesapHareketAna ON CariKartAna.LOGICALREF = CariHesapHareketAna.CLIENTREF
INNER JOIN LKSDB.dbo.LG_214_01_GNTOTCL AS CariSonBakiyeAna ON CariKartAna.LOGICALREF = CariSonBakiyeAna.CARDREF
GROUP BY
CariKartAna.DEFINITION_,
CariKartAna.CODE,
CariKartAna.SPECODE4,
CariHesapHareketAna.AMOUNT,
CariHesapHareketAna.TRCODE,
CariSonBakiyeAna.DEBIT / 2,
CariSonBakiyeAna.DEBIT,
CariSonBakiyeAna.TOTTYP,
CariSonBakiyeAna.DEBIT - CariSonBakiyeAna.CREDIT,
CariSonBakiyeAna.CREDIT,
CariHesapHareketAna.DATE_
HAVING (CariKartAna.SPECODE4 <> 'S') AND (CariHesapHareketAna.TRCODE = 14) AND (CariSonBakiyeAna.TOTTYP = 1)
