+- Delphi Can (https://www.delphican.com)
+-- Forum: Delphi (https://www.delphican.com/forumdisplay.php?fid=3)
+--- Forum: Genel Programlama (https://www.delphican.com/forumdisplay.php?fid=6)
+--- Konu Başlığı: Stok Karlılık Raporu (NETSIS) (/showthread.php?tid=7269)
Stok Karlılık Raporu (NETSIS) - OZCANK - 14-04-2023
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.
Cvp: Stok Karlılık Raporu (NETSIS) - elixir84 - 14-04-2023
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
Cvp: Stok Karlılık Raporu (NETSIS) - OZCANK - 14-04-2023
(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.