• Merhaba Ziyaretçi,
    Microsoft 365 Uygulamaları ile ilgili yeni haberler, dikkat çekici konular, ilgi ile takip edeceğiniz yazılar için.

    Abone Olun
  • ESTE - Microsoft Office Eğitimleri

    Yeni yıl Microsoft Office Eğitim planlarınız için bütçenizi oluşturmadan önce ESTE eğitim kalitesi ile tanışın. 🙌
    Kullanıcıların ihtiyacı olan yazılı materyal, dosya ve video kaynağı desteğimiz ile tüm ofis çalışanlarının iş süreçlerini rahatlatacak eğitimler planlayın. 🎯
    Microsoft Office eğitimlerimiz hakkında detaylı bilgi için bize ulaşın.

    👉 Microsoft Office Eğitim Talebi

SQL Sorgu Malzemelerin Ambarlardaki Eldeki Stok Durumu

stargate67

İsimsiz
Katılım
27 Şub 2020
Mesajlar
59
En iyi yanıt
0
Puanları
8
Yaş
49
Konum
istanbul
Ad Soyad
Mustafa Ülgen
Merhabalar,

Ambarlar ile ilgili değişiklikler yapılması gerekiyor, Sorgu ilgili firmaya uyarlama yapılarak yazılmış sizde kendi firmanıza uyarlamanız lazım.
üstadım böyle yaptım , doğru değil mi bu sorguda boş çıkıyor
SELECT URUN.CODE AS 'ÜRÜN KODU',
URUN.NAME AS 'ÜRÜN ADI',
'GENEL '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
LG_717_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_717_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_717_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_717_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'1'

ORDER BY URUN.CODE
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
Merhaba,

Ambarları kendinize göre ayarlayın.

SQL:
SELECT URUN.CODE AS 'ÜRÜN KODU',
URUN.NAME AS 'ÜRÜN ADI',
'GENEL '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
LG_006_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_006_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_006_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'22'

ORDER BY URUN.CODE
 

stargate67

İsimsiz
Katılım
27 Şub 2020
Mesajlar
59
En iyi yanıt
0
Puanları
8
Yaş
49
Konum
istanbul
Ad Soyad
Mustafa Ülgen
Merhaba,

Ambarları kendinize göre ayarlayın.

SQL:
SELECT URUN.CODE AS 'ÜRÜN KODU',
URUN.NAME AS 'ÜRÜN ADI',
'GENEL '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
LG_006_ITEMS URUN
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LV_006_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_006_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
ALIS ON URUN.LOGICALREF = ALIS.CARDREF
LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_006_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'22'

ORDER BY URUN.CODE
hocam alış satış fiyatları çıkmaması ve değerlerinin sıfır çıkması ambarlardan mı kaynaklanır .?
 

BURHAN KIDIR

Yeni Üye
Katılım
3 Eki 2020
Mesajlar
25
En iyi yanıt
0
Puanları
3
Yaş
38
Konum
BATMAN
Ad Soyad
BURHAN KIDIR
Merhaba Hocam bu sorun neden çıkar :)
 

Ekli dosyalar

  • Adsız.png
    Adsız.png
    9.5 KB · Görüntüleme: 6

deniza

Yeni Üye
Katılım
21 Eyl 2020
Mesajlar
2
En iyi yanıt
0
Puanları
1
Yaş
42
Konum
ist
Ad Soyad
deniz avvuran
Selamlar,
kodlar için teşekkürler. benim elimdeki data da LG_717_01_STINVTOT tablosunu sorguladığımda boş gelmektedir.
Haliyle kod da çalışmamaktadır. sanırım maliyetlendirme yapılmadığından kaynaklanıyor. aynı formatta ambarlara göre kalan stok miktarlarını göstermek için alternatif kod yazma şansımız var mı? Yoksa illaki LG_717_01_STINVTOT tablosunun içeriği dolu mu olmalı?
 

fthcndr

Yeni Üye
Katılım
21 Şub 2021
Mesajlar
12
En iyi yanıt
0
Puanları
3
Yaş
39
Konum
Ankara
Web sitesi
www.wptrseo.com
Ad Soyad
Fatih ÇANDIR
Office Vers.
Office 2019
Selamlar,
kodlar için teşekkürler. benim elimdeki data da LG_717_01_STINVTOT tablosunu sorguladığımda boş gelmektedir.
Haliyle kod da çalışmamaktadır. sanırım maliyetlendirme yapılmadığından kaynaklanıyor. aynı formatta ambarlara göre kalan stok miktarlarını göstermek için alternatif kod yazma şansımız var mı? Yoksa illaki LG_717_01_STINVTOT tablosunun içeriği dolu mu olmalı?
Merhabalar, aynı sorun bende de var. Yardımcı olabilirseniz sevinirim.
 

fthcndr

Yeni Üye
Katılım
21 Şub 2021
Mesajlar
12
En iyi yanıt
0
Puanları
3
Yaş
39
Konum
Ankara
Web sitesi
www.wptrseo.com
Ad Soyad
Fatih ÇANDIR
Office Vers.
Office 2019
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT URUN.CODE AS 'ÜRÜN KODU',
    URUN.NAME AS 'ÜRÜN ADI',
    'GENEL  '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
    'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
    'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
    'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
    'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
    'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
    'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
    'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
    'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
    'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
     LG_001_ITEMS URUN
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
    AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
    AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
    AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
    AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
    AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
    AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
    ALIS ON URUN.LOGICALREF = ALIS.CARDREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
    SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'10'

ORDER BY URUN.CODE
[/HIDEREPLYTHANKS]

Sonuç Tablo

Ekli dosyayı görüntüle 393
Emeğinize sağlık
 

nagdas

Yeni Üye
Katılım
2 Ara 2019
Mesajlar
9
En iyi yanıt
0
Puanları
1
Yaş
40
Konum
ÇORUM
Ad Soyad
neşet ağdaş
Merhaba, bu tabloya malzeme kartı içerisindeki "Alternatifler" kısmınıda ekleyebilir miyiz.
 

agalday1

Yeni Üye
Katılım
11 Şub 2024
Mesajlar
1
En iyi yanıt
0
Puanları
1
Yaş
45
Konum
ankara
Ad Soyad
ali demir
Office Vers.
2016
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT URUN.CODE AS 'ÜRÜN KODU',
    URUN.NAME AS 'ÜRÜN ADI',
    'GENEL  '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
    'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
    'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
    'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
    'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
    'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
    'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
    'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
    'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
    'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
     LG_001_ITEMS URUN
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
    AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
    AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
    AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
    AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
    AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
    AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
    ALIS ON URUN.LOGICALREF = ALIS.CARDREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
    SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'10'

ORDER BY URUN.CODE
[/HIDEREPLYTHANKS]

Sonuç Tablo

Ekli dosyayı görüntüle 393
Merhaba
Murat bey bu
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT URUN.CODE AS 'ÜRÜN KODU',
    URUN.NAME AS 'ÜRÜN ADI',
    'GENEL  '= CASE WHEN (AMBARM.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR END,
    'AMBAR 1'= CASE WHEN (AMBAR1.MIKTAR IS NULL) THEN '0' WHEN (AMBAR1.MIKTAR IS NOT NULL) THEN AMBAR1.MIKTAR END,
    'AMBAR 2'= CASE WHEN (AMBAR2.MIKTAR IS NULL) THEN '0' WHEN (AMBAR2.MIKTAR IS NOT NULL) THEN AMBAR2.MIKTAR END,
    'AMBAR 3'= CASE WHEN (AMBAR3.MIKTAR IS NULL) THEN '0' WHEN (AMBAR3.MIKTAR IS NOT NULL) THEN AMBAR3.MIKTAR END,
    'AMBAR 4'= CASE WHEN (AMBAR4.MIKTAR IS NULL) THEN '0' WHEN (AMBAR4.MIKTAR IS NOT NULL) THEN AMBAR4.MIKTAR END,
    'AMBAR 5'= CASE WHEN (AMBAR5.MIKTAR IS NULL) THEN '0' WHEN (AMBAR5.MIKTAR IS NOT NULL) THEN AMBAR5.MIKTAR END,
    'A.FIYAT'= CASE WHEN (ALIS.MIKTAR IS NULL) THEN '0' WHEN (ALIS.MIKTAR IS NOT NULL) THEN ALIS.MIKTAR END,
    'S.FIYAT'= CASE WHEN (SATIS.MIKTAR IS NULL) THEN '0' WHEN (SATIS.MIKTAR IS NOT NULL) THEN SATIS.MIKTAR END,
    'A.DEGER'= CASE WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * ALIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * ALIS.MIKTAR END,
    'S.DEGER'= CASE WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NULL) THEN '0' WHEN (AMBARM.MIKTAR * SATIS.MIKTAR IS NOT NULL) THEN AMBARM.MIKTAR * SATIS.MIKTAR END

FROM
     LG_001_ITEMS URUN
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=-1 GROUP BY STOCKREF)
    AMBARM ON URUN.LOGICALREF = AMBARM.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=0 GROUP BY STOCKREF)
    AMBAR1 ON URUN.LOGICALREF = AMBAR1.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=1 GROUP BY STOCKREF)
    AMBAR2 ON URUN.LOGICALREF = AMBAR2.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=2 GROUP BY STOCKREF)
    AMBAR3 ON URUN.LOGICALREF = AMBAR3.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=3 GROUP BY STOCKREF)
    AMBAR4 ON URUN.LOGICALREF = AMBAR4.STOCKREF
    LEFT JOIN (SELECT SUM(ONHAND) MIKTAR,STOCKREF FROM LG_001_01_STINVTOT WHERE INVENNO=4 GROUP BY STOCKREF)
    AMBAR5 ON URUN.LOGICALREF = AMBAR5.STOCKREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=1 GROUP BY CARDREF))
    ALIS ON URUN.LOGICALREF = ALIS.CARDREF
    LEFT JOIN ((SELECT SUM(PRICE) AS 'MIKTAR',CARDREF FROM LG_001_PRCLIST WHERE PTYPE=2 GROUP BY CARDREF))
    SATIS ON URUN.LOGICALREF=SATIS.CARDREF

WHERE URUN.CARDTYPE<>'10'

ORDER BY URUN.CODE
[/HIDEREPLYTHANKS]

Sonuç Tablo

Ekli dosyayı görüntüle 393
Merhaba Murat bey bu sorguya tarih ekleyebilirmiyiz. Teşekkür ederim.
 
Üst Alt