Logo muhasebe programında yaşlandırma sorgusu örneği aşağıdaki gidir.
Sorguda kullanılan fonksiyon için lütfen iletişime geçiniz..
select
*
from (
SELECT
CLNTC.CODE AS CariKodu,
(CLNTC.DEFINITION_) AS CariAdi
, CAST(SUM( (CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '1' THEN(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) END)) AS DECIMAL(18,2)) AS Borc
, CAST(SUM(CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '0' THEN(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) END) AS DECIMAL(18,2)) AS Alacak
,CAST(SUM((CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '1' THEN(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) END)) -
SUM(CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '0' THEN(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) END) AS DECIMAL(18,2)) Bakiye
, MAX(CLNTC.CITY) Sehir
,(select top(1) VADE_GUNU from IDF_Yaslandirma(CLNTC.CODE,'H')) as VadeGunu
,(select SUM(CASE WHEN 90-VADE_FARKI between 0 and 30 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [0-30]
,(select SUM(CASE WHEN 90-VADE_FARKI between 31 and 60 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [31-60]
,(select SUM(CASE WHEN 90-VADE_FARKI between 61 and 90 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [61-90]
,(select SUM(CASE WHEN 90-VADE_FARKI between 83 and 90 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [83-90]
,(select SUM(CASE WHEN 90-VADE_FARKI > 90 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [90+]
,(select SUM(CASE WHEN 90-VADE_FARKI < 0 THEN TAHSILAT ELSE 0 END) from IDF_Yaslandirma(CLNTC.CODE,'H')) as [Vadesi Geçmemiş]
,CLNTC.TELNRS1 as Telefon1
,CLNTC.TELNRS2 as Telefon2
,CLNTC.SPECODE as [Bölge]
FROM GO3.dbo.LG_XXX_01_CLFLINE AS CTRNS WITH (NOLOCK)
LEFT OUTER JOIN GO3.dbo.LG_XXX_01_INVOICE WITH (NOLOCK) ON CTRNS.CLIENTREF = GO3.dbo.LG_XXX_01_INVOICE.CLIENTREF AND CTRNS.SOURCEFREF = GO3.dbo.LG_XXX_01_INVOICE.LOGICALREF
LEFT OUTER JOIN GO3.dbo.L_CAPIDIV WITH (NOLOCK) ON CTRNS.BRANCH = L_CAPIDIV.NR
LEFT OUTER JOIN GO3.dbo.LG_XXX_01_CLFICHE AS CLFIC WITH (NOLOCK) ON CTRNS.SOURCEFREF = CLFIC.LOGICALREF
LEFT OUTER JOIN GO3.dbo.LG_XXX_CLCARD AS CLNTC WITH(NOLOCK) ON CTRNS.CLIENTREF = CLNTC.LOGICALREF
LEFT OUTER JOIN GO3.dbo.LG_SLSMAN AS LG_SLSMAN_1 WITH (NOLOCK) ON GO3.dbo.LG_XXX_01_INVOICE.SALESMANREF = LG_SLSMAN_1.LOGICALREF
WHERE 1=1
and (L_CAPIDIV.FIRMNR = XXX)
AND CLNTC.CODE LIKE '120%'
AND CTRNS.CANCELLED = 0
GROUP BY CTRNS.BRANCH, CLNTC.CODE,CLNTC.DEFINITION_, CTRNS.CLIENTREF,CLNTC.TELNRS1,CLNTC.TELNRS2,CLNTC.SPECODE
HAVING CAST(SUM((CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '1' THEN(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) END)) -
SUM(CASE WHEN LG_XXX_01_INVOICE.FROMKASA = 1 AND CTRNS.SIGN = '0' THEN(CASE WHEN CTRNS.SIGN = '0' THEN CTRNS.AMOUNT WHEN CTRNS.SIGN = '1' THEN '0' ELSE '999999999' END) ELSE(CASE WHEN CTRNS.SIGN = '0' THEN '0' WHEN CTRNS.SIGN = '1' THEN CTRNS.AMOUNT ELSE 999999999 END) END) AS DECIMAL(18,2))
>= 200
) YKYAZILIM
Comments