SentezPositive Detaylı Satış Raporu SQL Sorgusu
Select SK.KOD as [Stok Kodu],SK.ADI as [Stok Adı],'USD' as [Döviz]
,round(isnull(Sum(S3.MIKTAR),0), 2) As [Miktar], round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2) As [Tutar]
,SK.AFIYAT1 as [Alış],round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT1, 2) as [SMM]
,round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2)-round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT1, 2) as [Kar Tutarı]
,case when (isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT1) <> 0 then round(((isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0)/(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT1))*100)-100, 2)
else 0 end as [Kar Oranı]
From SKART SK
inner join SFISD S3 on (S3.MKOD = SK.KOD And S3.SIRKET = SK.SIRKET and rtrim(S3.DOVIZ)='USD' and S3.TIP In (20, 21))
left join SFIS SF3 on (SF3.SIRKET = S3.SIRKET And SF3.TIP = S3.TIP And S3.FISNO = S3.FISNO)
group by SK.KOD,SK.ADI,SK.AFIYAT1,SK.AFIYAT2,SK.AFIYAT3
union
select SK.KOD as [Stok Kodu],SK.ADI as [Stok Adı],'EUR' as [Döviz]
,round(isnull(Sum(S3.MIKTAR),0), 2) As [Miktar], round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2) As [Tutar]
,SK.AFIYAT2 as [Alış],round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT2, 2) as [SMM]
,round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2)-round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT2, 2) as [Kar Tutarı]
,case when (isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT2) <> 0 then round(((isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.DFIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0)/(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT2))*100)-100, 2)
else 0 end as [Kar Oranı]
From SKART SK
inner join SFISD S3 on (S3.MKOD = SK.KOD And S3.SIRKET = SK.SIRKET and rtrim(S3.DOVIZ)='EUR' and S3.TIP In (20, 21))
left join SFIS SF3 on (SF3.SIRKET = S3.SIRKET And SF3.TIP = S3.TIP And S3.FISNO = S3.FISNO)
group by SK.KOD,SK.ADI,SK.AFIYAT1,SK.AFIYAT2,SK.AFIYAT3
union
select SK.KOD as [Stok Kodu],SK.ADI as [Stok Adı],'YTL' as [Döviz]
,round(isnull(Sum(S3.MIKTAR),0), 2) As [Miktar], round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.FIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2) As [Tutar]
,SK.AFIYAT3 as [Alış],round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT3, 2) as [SMM]
,round(isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.FIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0), 2)-round(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT3, 2) as [Kar Tutarı]
,case when (isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT3) <> 0 then round(((isnull(Sum(dbo.fn_CalcNetAmount(S3.MIKTAR,
S3.FIYAT, S3.KDVDH, S3.KDV, S3.ISKTUTAR, S3.ISK1, S3.ISK2, S3.ISK3, SF3.ISKONTOT,
SF3.TOPLAM, SF3.KDVT, SF3.MASRAF, SK.CARPAN1, SK.BOLEN1, SK.CARPAN2, SK.BOLEN2, S3.BRM)),0)/(isnull(Sum(S3.MIKTAR),0)*SK.AFIYAT3))*100)-100, 2)
else 0 end as [Kar Oranı]
From SKART SK
inner join SFISD S3 on (S3.MKOD = SK.KOD And S3.SIRKET = SK.SIRKET and rtrim(S3.DOVIZ)='YTL' and S3.TIP In (20, 21))
left join SFIS SF3 on (SF3.SIRKET = S3.SIRKET And SF3.TIP = S3.TIP And S3.FISNO = S3.FISNO)
group by SK.KOD,SK.ADI,SK.AFIYAT1,SK.AFIYAT2,SK.AFIYAT3
order by SK.KOD,[Döviz]