SentezPositive Cari Hesap - Stok Dövizli Satış Raporu SQL Sorgusu
Select SD.CKOD as [Cari Hesap Kodu],isnull(CK.ADI,'') as [Cari Hesap Adı],SD.MKOD as [Stok Kodu],isnull(SK.ADI,'') as [Stok Adı]
,round(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0)),2) as [Satış Miktarı]
,round(sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3, S.ISKONTOT, S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0)),2) as [Satış Tutarı]
,round(isnull(SK.AFIYAT1,0),2) as [Birim Maliyet]
,round(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1,2)
as [Satış Maliyeti]
--,round(sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3, S.ISKONTOT, S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))
-- -(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1),2)
as [Kar/Zarar]
,case when
round(sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3, S.ISKONTOT, S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))
-(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1),2)>=0
then round(sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3,
S.ISKONTOT, S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))
-(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1),2) else 0 end
as [Kar]
,case when
round(sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3, S.ISKONTOT, S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))
-(sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1),2)>0
then 0
else round((sum(isnull(dbo.fn_CalcQuantity1(SD.MIKTAR, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0))*SK.AFIYAT1)-
sum(isnull(dbo.fn_CalcNetAmount(SD.MIKTAR, SD.DFIYAT, SD.KDVDH, SD.KDV, SD.ISKTUTAR, SD.ISK1, SD.ISK2, SD.ISK3, S.ISKONTOT,
S.TOPLAM, S.KDVT, S.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, SD.BRM),0)),2) end as [Zarar]
from SFISD SD
left join SFIS S on (SD.SIRKET=S.SIRKET and S.TIP=SD.STIP and S.FISNO=SD.FISNO)
left join SKART SK on (SD.SIRKET=SK.SIRKET and SD.MKOD=SK.KOD)
left join CKART CK on (SD.SIRKET=CK.SIRKET and SD.CKOD=CK.KOD)
where SD.TIP=20
group by SD.CKOD,SD.MKOD,SK.ADI,SK.AFIYAT1,CK.ADI
order by SD.CKOD,SD.MKOD
Not: Bu rapor dfiyat üzerinden çalışıyor ve afiyat1 maliyet kabul ediliyor.