Stok Karlılık Raporu (NETSIS)

[OZCANK]

Arkadaşlar Merhaba; Hayırlı Ramazanlar.


Ben Netsis de Stok karlılık raporu almak istiyorum bir tane view yazdım ama bir yerde hata veriyor yardımcı olabilirmisiniz?

SELECT T1.STOK_KODU AS StokKodu ,T1.STOK_ADI AS StokAdi ,T1.OLCU_BR1 AS Br
--Giren Miktar ve Giren Tutar
,(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G') AS GirenMiktar
,(SELECT SUM(STHAR_GCMIK * STHAR_NF) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G') AS GirenTutar
--Çýkan Miktar ve Çýkan Tutar
,(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='C')as CikanMiktar
,(SELECT SUM(STHAR_GCMIK * STHAR_NF) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='C') AS CikisTutar
--Maliyet Birim Fiyati
,(SELECT SUM(STHAR_NF * STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M')) /
(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M')) AS MaliyetFiyati
--- Maliyettutari
,(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='C' * ((SELECT SUM(STHAR_GCMIK*STHAR_NF) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M'))
/
(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M')))) AS MaliyetTutari 
--– Kar
,(SELECT SUM(STHAR_GCMIK*STHAR_NF) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='C' - ((SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='C')*
((SELECT SUM(STHAR_GCMIK*STHAR_NF) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M'))
/
(SELECT SUM(STHAR_GCMIK) FROM TBLSTHAR WHERE T1.STOK_KODU=STOK_KODU AND STHAR_GCKOD='G')))) as Kar
 FROM TBLSTSABIT T1

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

[elixir84]

SELECT T1.STOK_KODU AS StokKodu ,T1.STOK_ADI AS StokAdi ,T1.OLCU_BR1 AS Br
--Giren Miktar ve Giren Tutar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END) GirenMiktar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) GirenTutar
--Çýkan Miktar ve Çýkan Tutar
,SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) CikanMiktar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) CikisTutar
--Maliyet Birim Fiyati
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END MaliyetFiyati
--- Maliyettutari
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) * SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END MaliyetTutari
--– Kar
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) - (SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) * SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END)) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END Kar
FROM TBLSTSABIT T1 INNER JOIN TBLSTHAR T2 ON T1.STOK_KODU=T2.STOK_KODU
GROUP BY 
T1.STOK_KODU
,T1.STOK_ADI
,T1.OLCU_BR1

[OZCANK]

(14-04-2023, Saat: 14:15)elixir84 Adlı Kullanıcıdan Alıntı:

SELECT T1.STOK_KODU AS StokKodu ,T1.STOK_ADI AS StokAdi ,T1.OLCU_BR1 AS Br
--Giren Miktar ve Giren Tutar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END) GirenMiktar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) GirenTutar
--Çýkan Miktar ve Çýkan Tutar
,SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) CikanMiktar
,SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) CikisTutar
--Maliyet Birim Fiyati
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END MaliyetFiyati
--- Maliyettutari
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) * SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END MaliyetTutari
--– Kar
,CASE WHEN SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END)<>0 THEN
SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK*STHAR_NF ELSE 0 END) - (SUM(CASE WHEN T2.STHAR_GCKOD='C' THEN STHAR_GCMIK ELSE 0 END) * SUM(CASE WHEN T2.STHAR_GCKOD='G' AND STHAR_HTUR NOT IN('L','M') THEN STHAR_GCMIK*STHAR_NF ELSE 0 END)) /
SUM(CASE WHEN T2.STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE 0 END)
ELSE 0 END Kar
FROM TBLSTSABIT T1 INNER JOIN TBLSTHAR T2 ON T1.STOK_KODU=T2.STOK_KODU
GROUP BY 
T1.STOK_KODU
,T1.STOK_ADI
,T1.OLCU_BR1

Teşekkür ederim.