SQL Sorgu Malzemelerin Ambarlardaki Eldeki Stok Durumu

stargate67 · 29 Şub 2020

Vedat ÖZER' Alıntı:
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 · 29 Şub 2020

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

ergulhoca · 29 Şub 2020

Admin' Alıntı:
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[Gizli içerik]

Sonuç Tablo

Ekli dosyayı görüntüle 393

teşekkürler

stargate67 · 2 Mar 2020

Vedat ÖZER' Alıntı:

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 .?

Vedat ÖZER · 2 Mar 2020

Ambar numarası ve o ambarda veri yok ise sıfır cikar

samir153 · 3 Mar 2020

Tesekkur ederim.

mith · 14 Nis 2020

Teşekkürler

karzen · 16 Nis 2020

bende deneyeceğim

leon29 · 11 Tem 2020

Murat OSMA' Alıntı:
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[Gizli içerik]

Sonuç Tablo

Ekli dosyayı görüntüle 393

tskler

hllkrdmr · 28 Eyl 2020

Teşekkürler

Furbist · 30 Eyl 2020

Thanks a lot.

BURHAN KIDIR · 3 Eki 2020

Murat OSMA' Alıntı:
Logo'da Malzemelerin Ambarlardaki Eldeki Stok durum tablosu için bu sorguyu kullanabilirsiniz.

[Gizli içerik]

Sonuç Tablo

Ekli dosyayı görüntüle 393

Raporu beğendim elinize sağlık, Faydalı olmuş..

BURHAN KIDIR · 3 Eki 2020

Merhaba Hocam bu sorun neden çıkar

deniza · 3 Eki 2020

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 · 21 Şub 2021

deniza' Alıntı:
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.

Vedat ÖZER · 21 Şub 2021

Merhabalar

LV_717_01_STINVTOT olarak deneyin

fthcndr · 28 Mar 2021

Murat OSMA' Alıntı:

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 · 29 Kas 2021

Merhaba, bu tabloya malzeme kartı içerisindeki "Alternatifler" kısmınıda ekleyebilir miyiz.

agalday1 · 11 Şub 2024

Murat OSMA' Alıntı:

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

Murat OSMA' Alıntı:

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.

ugurozcan1988 · 12 Eyl 2024

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @columns = STUFF((SELECT ',' + QUOTENAME(NAME)
FROM godb.dbo.L_CAPIWHOUSE L_CAP where L_CAP.FIRMNR ='224'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @sql = '
SELECT URUNKODU,URUNADI, ' + @columns + '
FROM(
SELECT ITS.CODE URUNKODU, ITS.NAME URUNADI, L_CAP.NAME AS NAME, SUM(STINVTOT.ONHAND) AS MIKTAR
FROM godb.dbo.LV_224_01_STINVTOT AS STINVTOT LEFT OUTER JOIN
godb.dbo.LG_224_ITEMS AS ITS ON STINVTOT.STOCKREF = ITS.LOGICALREF
LEFT OUTER JOIN godb.dbo.L_CAPIWHOUSE L_CAP ON L_CAP.NR = STINVTOT.INVENNO AND L_CAP.FIRMNR = ''224''
WHERE (ITS.CARDTYPE = 1) and STINVTOT.DATE_<= ''2024-07-31''
GROUP BY ITS.CODE, ITS.NAME,L_CAP.NAME, ITS.CARDTYPE
HAVING (SUM(STINVTOT.ONHAND) <> 0)
) as SourceTable
PIVOT(
SUM(MIKTAR)
FOR NAME IN (' + @columns + ')
) AS PivotTable; ';
EXEC sp_executesql @sql; ;

Ambar bilgilerini dinamik olarak çekebilirsiniz

SQL Sorgu Malzemelerin Ambarlardaki Eldeki Stok Durumu

Daha fazla seçenek

stargate67

Vedat ÖZER

ergulhoca

stargate67

Vedat ÖZER

samir153

mith

karzen

leon29

hllkrdmr

Furbist

BURHAN KIDIR

BURHAN KIDIR

Ekli dosyalar

deniza

fthcndr

Vedat ÖZER

fthcndr

nagdas

agalday1

ugurozcan1988