EXTEND STOK BAKIYE RAPORU

selam arkadaslar;

asagidaki cümleyi yazdim fakat bir yerde takildim yardimci olursaniz sevinirim..

ay ay aysonu stok'u ortalama fiyat ile çarpip tutarini buluyor. fakat hareket olmayan aylarda da rakam yaziyor mesala temmuz ayinda hareket var ise rakam yazmasini istiyorum hareket yok ise bisey yazmasin...

simdiden tesekkür ederim..

 

 

SELECT
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '1' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '1' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_NF) AS OCAK,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '2' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '2' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_NF) AS SUBAT,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '3' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '3' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS MART,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '4' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '4' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS NISAN,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '5' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '5' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS MAYIS,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '6' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '6' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS HAZIRAN,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '7' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '7' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS TEMMUZ,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '8' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '8' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS AGUSTOS,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '9' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '9' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS EYLUL,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '10' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '10' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS EKIM,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '11' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '11' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS KASIM,
SUM((CASE WHEN TBLSTHAR.STHAR_GCKOD='G' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '12' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)-(CASE WHEN TBLSTHAR.STHAR_GCKOD='C' AND MONTH(TBLSTHAR.STHAR_TARIH) BETWEEN '1'AND '12' AND TBLSTHAR.SUBE_KODU='0' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END))*AVG(TBLSTHAR.STHAR_BF) AS ARALIK
FROM TBLSTHAR WHERE TBLSTHAR.STOK_KODU LIKE 'A%' AND YEAR (TBLSTHAR.STHAR_TARIH)='2010'

 

NETSISSQLSTR("SELECT top 1 (STHAR_TARIH) FROM TBLSTHAR WHERE STOK_KODU=$1 AND DEPO_KODU=1 AND STHAR_CARIKOD='0001' AND STHAR_GCKOD='G' AND STHAR_HTUR IN ('J','L') ORDER BY STHAR_TARIH desc")

Sayin moderatörüm asagidaki verdiginiz formulun sonucuna göre son giren miktarida(kl) çikartmak istiyorum ama beceremedim.Formul sonucu örnegin 13.10.2010 bu tarihe göre gelen miktari çikaramiyorum.normalde tarihi 2010-10-13 yazdigimda farkli formulde sonucu aliyorum.yardimci olabilirmisiniz.

 

 

 

NETSISSQLSTR("SELECT top 1 (STHAR_TARIH) FROM TBLSTHAR WHERE STOK_KODU=$1 AND DEPO_KODU=1 AND STHAR_CARIKOD='0001' AND STHAR_GCKOD='G' AND STHAR_HTUR IN ('J','L') ORDER BY STHAR_TARIH desc")

 

Çok fazla oldugumun farkindayim insaallah bu cahil insana kizmazsiniz.yazdiginizi dediginiz gibi netsissql ye çevirerek yaptim olduda tesekkürler.ancak ayni günde 2 faturada veya ayni faturanin ayri satirlarinda stoga girmis ürünün 1 tanesini aliyor.bunu örnegin 13.10.2010 da stoga girmis toplam olarak alabilirmiyiz.

 

Birde sgl kodlari ile ilgili neyi nerede nasil kullacagimi bilmiyorum, en azindan kendi isimi görecek kadar isime hakim olmak için egitimini almak istiyorum.Bu konuda önerilerinizi veya yapabilecekleriniz konusunda yardimlarini rica ediyorum.

 

saygilarimla