SATILMAYAN MALLAR LISTESI-----ACILL

[smoke]

Merhaba,
Sqlden x tarıhınden ıtıbaren satılmayan mallar lıstesı  verebılecek sorgu lazım arkadaslar

[smoke]

Benım ıcın cok zor geldı bu rapor ama o kadar uzman hocaların ıcınde bırı yardımcı olur dıye dusunuyorum

[ARSLAN_Aslan]

Bu Sorguyu bir dener misin ?

SELECT     *
FROM         LG_507_ITEMS
WHERE     (LOGICALREF NOT IN
(SELECT     STOCKREF
 FROM          LG_507_01_STLINE
 WHERE      (DATE_ >= CONVERT(DATETIME, '2007-01-01 00:00:00', 102)) AND (TRCODE = 8 ) AND (INVOICEREF > 0)))
ORDER BY CODE


Bilgi : (TRCODE = 8 ) AND (INVOICEREF > 0) bu koşullar ile Satış Faturası Kesilip Kesilmediğine Bakıyor. Satış Fatura Şartı arıyor.

[smoke]

INVOICEREF I KALDIRIP IRSALIYE ILE CIKISLARIDA GOREBILIRIM.
TEST EDECEGIM TESEKKURLER

[ARSLAN_Aslan]

Sonucu bekliyorum.

Kolay gelsin.

[SertacSolmaz]

Hocam paylastığınız kodu bayi bazlı hale nasıl getiriz ?

[MehmetYavuz]

SELECT   CLCARD.CODE, CLCARD.DEFINITION_, ITEMS.CODE, ITEMS.NAME
FROM   LG_001_ITEMS ITEMS (NOLOCK), LG_001_CLCARD CLCARD
WHERE   CLCARD.CODE+'_'+ITEMS.CODE NOT IN (
   SELECT   CLCARD.CODE+'_'+ITEMS.CODE
   FROM   LG_001_01_STLINE STLINE (NOLOCK)
      LEFT JOIN LG_001_ITEMS ITEMS (NOLOCK) ON ITEMS.LOGICALREF=STLINE.STOCKREF
      LEFT JOIN LG_001_CLCARD CLCARD (NOLOCK) ON CLCARD.LOGICALREF=STLINE.CLIENTREF
   WHERE   STLINE.CANCELLED=0
         AND CLCARD.CODE LIKE '120.01%'
         AND STLINE.DATE_ BETWEEN '20120101' AND '20121231'
         AND STLINE.TRCODE IN ( 8 )
   GROUP BY CLCARD.CODE+'_'+ITEMS.CODE)
ORDER BY CLCARD.CODE,ITEMS.CODE

[SertacSolmaz]

Hocam her türlü şekle soktum kodu ama genede sonuç 0

[MehmetYavuz]

2012'DE 8 fişiyle hareket gören cari ve stoklar var. üstteki kısımda olmayanları listeliyor. buna rağmen sıfır geliyorsa, bir yıl boyunca her carin her ürünü enaz bir kere almış demektir.
özetle, Datana bakmak lazım.

[SertacSolmaz]

2012'DE 8 fişiyle hareket gören cari ve stoklar var. üstteki kısımda olmayanları listeliyor. buna rağmen sıfır geliyorsa, bir yıl boyunca her carin her ürünü enaz bir kere almış demektir.
özetle, Datana bakmak lazım.

İşte en az 1 kere almış olmaları mümkün değil, ayrıca tarih aralığınıda 1 aya çekdim denedim, gene sonuç 0 döndü..


Misal satış rakamlarıma şu kodla ulaşabiliyorum :

Kod: [Seç]


USE [TIGER]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE    VIEW [dbo].[NTS_SATISLAR_2012_MS_BAYILERI] AS

SELECT '32' AS FIRMANO,
LG_032_01_INVOICE.FICHENO AS FATURANO,    -- LG_031_01_INVOICE.FICHENO AS FATURANO,
LG_032_01_INVOICE.TRCODE AS FATURATIPI,   -- LG_031_01_INVOICE.TRCODE AS FATURATIPI,
LG_032_ITMUNITA.WEIGHT AS KOLILITRE,      -- LG_031_ITMUNITA.WEIGHT AS KOLILITRE,
LG_032_ITEMS.CODE STOKKODU,               -- LG_031_ITEMS.CODE STOKKODU,
--LG_031_ITEMS.NAME STOKADI,              --STOK ADLARINI 2011 DATASINDAN ALIYOR. LG_031_ITEMS.NAME STOKADI,
LG_032_ITEMS.NAME STOKADI,
LG_032_ITEMS.STGRPCODE,                   -- LG_031_ITEMS.STGRPCODE,
LG_032_ITEMS.SPECODE AS URUN_GRUBU,       -- LG_031_ITEMS.SPECODE AS URUN_GRUBU,
'KOLI' AS BIRIM,                          -- 'KOLI' AS BIRIM,
  ISNULL((SELECT CAST(PL.DAY_ AS INT) FROM  LG_032_PAYPLANS P, LG_032_PAYLINES PL WHERE P.LOGICALREF=PL.PAYPLANREF AND P.LOGICALREF= LG_032_01_INVOICE.PAYDEFREF),0) AS PAYTYPE,
LG_032_CLCARD.CODE AS MUSTERIKODU,   -- LG_031_CLCARD.CODE AS MUSTERIKODU,
LG_032_CLCARD.DEFINITION_ AS MUSTERIADI,  -- LG_031_CLCARD.DEFINITION_ AS MUSTERIADI,
LG_032_CLCARD.SPECODE AS BOLGE,           --LG_031_CLCARD.SPECODE AS BOLGE,
LG_032_CLCARD.CITY AS SEHIR,
LG_SLSMAN.CODE AS PLASIYER_KODU,          --LG_SLSMAN.CODE AS PLASIYER_KODU,
LG_SLSMAN.DEFINITION_ AS PLASIYER_ADI,    -- LG_SLSMAN.DEFINITION_ AS PLASIYER_ADI,
LG_032_01_INVOICE.DATE_ AS FATURATARIHI,  --LG_031_01_INVOICE.DATE_ AS FATURATARIHI,


CASE
when month(LG_032_01_INVOICE.DATE_) between 1 and 3 then '1.DONEM'
when month(LG_032_01_INVOICE.DATE_) between 4 and 6 then '2.DONEM'
when month(LG_032_01_INVOICE.DATE_) between 7 and 9 then '3.DONEM'
when month(LG_032_01_INVOICE.DATE_) between 10 and 12 then '4.DONEM'
END AS DONEM,

Month(LG_032_01_INVOICE.DATE_) AS FATURAAYI,

((CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.AMOUNT ELSE 0 END) - (CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.AMOUNT ELSE 0 END)) AS NET_SATIS_MIKTAR,

CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.VATMATRAH ELSE 0 END AS FAT3,
CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.VATMATRAH ELSE 0 END AS FAT8,

CASE
when LG_032_ITEMS.CODE BETWEEN 100350 AND 100373 then 'KAT1'
when LG_032_ITEMS.CODE BETWEEN 100399 AND 100485 then 'KAT1'
    when LG_032_ITEMS.CODE BETWEEN 100001 AND 100269 then 'KAT2'
    when LG_032_ITEMS.CODE BETWEEN 100270 AND 100280 then 'KAT3'
    when LG_032_ITEMS.CODE BETWEEN 100281 AND 100297 then 'KAT2'
    when LG_032_ITEMS.CODE BETWEEN 100700 AND 101025 then 'KAT2'
    when LG_032_ITEMS.CODE BETWEEN 10057700 AND 10057706 then 'KAT2'
when LG_032_ITEMS.CODE BETWEEN 100397 AND 100398 then 'KAT4'
when LG_032_ITEMS.CODE BETWEEN 100500 AND 100650 then 'KAT2'
END AS KATEGORI,

CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.TOTAL ELSE 0 END AS IADE_TUTAR, --İADE TUTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.TOTAL ELSE 0 END AS SATIS_TUTAR, --SATIŞ TUTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.AMOUNT ELSE 0 END AS IADE_MIKTAR, --İADE MIKTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.AMOUNT ELSE 0 END AS SATIS_MIKTAR,  --SATIŞ MIKTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.DISTDISC ELSE 0 END AS IADE_INDIRIM_TUTAR, --IADE_INDIRIM_TUTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.DISTDISC ELSE 0 END AS SATIS_INDIRIM_TUTAR, --SATIS_INDIRIM_TUTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='3' THEN LG_032_01_STLINE.VATMATRAH ELSE 0 END AS NET_IADE_TUTAR, --NET İADE TUTAR
CASE WHEN LG_032_01_INVOICE.TRCODE ='8' THEN LG_032_01_STLINE.VATMATRAH ELSE 0 END AS NET_SATIS_TUTAR, --NET SATIŞ TUTAR

CASE WHEN lg_032_ITEMS.CODE BETWEEN 100001 AND 100014 THEN 1
WHEN lg_032_ITEMS.CODE BETWEEN 100015 AND 100024 THEN 2
WHEN lg_032_ITEMS.CODE BETWEEN 100025 AND 100039 THEN 3
WHEN lg_032_ITEMS.CODE BETWEEN 100040 AND 100054 THEN 4
WHEN lg_032_ITEMS.CODE BETWEEN 100055 AND 100064 THEN 5
WHEN lg_032_ITEMS.CODE BETWEEN 100065 AND 100079 THEN 6
WHEN lg_032_ITEMS.CODE BETWEEN 100080 AND 100089 THEN 7
WHEN lg_032_ITEMS.CODE BETWEEN 100090 AND 100099 THEN 8
WHEN lg_032_ITEMS.CODE BETWEEN 100100 AND 100109 THEN 9
WHEN lg_032_ITEMS.CODE BETWEEN 100110 AND 100120 THEN 10
WHEN lg_032_ITEMS.CODE BETWEEN 100145 AND 100149 THEN 11
WHEN lg_032_ITEMS.CODE BETWEEN 100150 AND 100159 THEN 12
WHEN lg_032_ITEMS.CODE BETWEEN 100160 AND 100169 THEN 13
WHEN lg_032_ITEMS.CODE BETWEEN 100170 AND 100184 THEN 14
WHEN lg_032_ITEMS.CODE BETWEEN 100185 AND 100194 THEN 15
WHEN lg_032_ITEMS.CODE BETWEEN 100195 AND 100200 THEN 16
WHEN lg_032_ITEMS.CODE BETWEEN 100201 AND 100202 THEN 17
WHEN lg_032_ITEMS.CODE BETWEEN 100240 AND 100254 THEN 18
WHEN lg_032_ITEMS.CODE BETWEEN 100255 AND 100264 THEN 19
WHEN lg_032_ITEMS.CODE BETWEEN 100265 AND 100268 THEN 20
WHEN lg_032_ITEMS.CODE IN ('100350','100355','100360','100485') THEN 21
WHEN lg_032_ITEMS.CODE IN ('100370','100371','100372','100373') THEN 22
WHEN lg_032_ITEMS.CODE IN ('100399','100400','100401','100402','105052') THEN 23
WHEN lg_032_ITEMS.CODE IN('105001','105002','105003','105004','105060') THEN 24
WHEN lg_032_ITEMS.CODE BETWEEN 105005 AND 105008 THEN 25
WHEN lg_032_ITEMS.CODE IN ('100270','100271','100272','100273','100274','100275','100276') THEN 26
WHEN lg_032_ITEMS.CODE IN ('100277','100278','100279','100280','106025') THEN 27
WHEN lg_032_ITEMS.CODE BETWEEN 100281 AND 100284 THEN 28
WHEN lg_032_ITEMS.CODE BETWEEN 100285 AND 100289 THEN 29
WHEN lg_032_ITEMS.CODE BETWEEN 100290 AND 100294 THEN 30
WHEN lg_032_ITEMS.CODE BETWEEN 100295 AND 100297 THEN 31
WHEN lg_032_ITEMS.CODE BETWEEN 100500 AND 100501 THEN 32
WHEN lg_032_ITEMS.CODE BETWEEN 100510 AND 100519 THEN 33
WHEN lg_032_ITEMS.CODE BETWEEN 100520 AND 100524 THEN 34
WHEN lg_032_ITEMS.CODE BETWEEN 100525 AND 100533 THEN 35
WHEN lg_032_ITEMS.CODE BETWEEN 100534 AND 100539 THEN 36
WHEN lg_032_ITEMS.CODE BETWEEN 100540 AND 100544 THEN 37
WHEN lg_032_ITEMS.CODE BETWEEN 100545 AND 100546 THEN 38
WHEN lg_032_ITEMS.CODE BETWEEN 100547 AND 100547 THEN 39
WHEN lg_032_ITEMS.CODE BETWEEN 100548 AND 100550 THEN 40
WHEN lg_032_ITEMS.CODE IN ('100551','100552','100553','100554','100555','100556','100575','100576') THEN 41
WHEN lg_032_ITEMS.CODE BETWEEN 100557 AND 100560 THEN 42
WHEN lg_032_ITEMS.CODE BETWEEN 100561 AND 100566 THEN 43
WHEN lg_032_ITEMS.CODE BETWEEN 100567 AND 100569 THEN 44
WHEN lg_032_ITEMS.CODE BETWEEN 100570 AND 100574 THEN 45
WHEN lg_032_ITEMS.CODE IN ('100577','10057700') THEN 46
WHEN lg_032_ITEMS.CODE IN ('10057701','10057702') THEN 47
WHEN lg_032_ITEMS.CODE IN ('10057703','10057704') THEN 48
WHEN lg_032_ITEMS.CODE IN ('10057705','10057706') THEN 49
WHEN lg_032_ITEMS.CODE BETWEEN 100580 AND 100585 THEN 50
WHEN lg_032_ITEMS.CODE BETWEEN 100590 AND 100595 THEN 51
WHEN lg_032_ITEMS.CODE BETWEEN 100596 AND 100597 THEN 52
WHEN lg_032_ITEMS.CODE BETWEEN 100601 AND 100605 THEN 53
WHEN lg_032_ITEMS.CODE BETWEEN 100611 AND 100615 THEN 54
WHEN lg_032_ITEMS.CODE BETWEEN 100620 AND 100620 THEN 55
WHEN lg_032_ITEMS.CODE BETWEEN 100625 AND 100629 THEN 56
WHEN lg_032_ITEMS.CODE BETWEEN 100631 AND 100631 THEN 57
WHEN lg_032_ITEMS.CODE BETWEEN 100632 AND 100636 THEN 58
WHEN lg_032_ITEMS.CODE BETWEEN 100640 AND 100644 THEN 59
WHEN lg_032_ITEMS.CODE BETWEEN 100680 AND 100683 THEN 60
WHEN lg_032_ITEMS.CODE IN ('1006789','10067900','100679','10067903','106060','106061','106063','106064') THEN 61
WHEN lg_032_ITEMS.CODE BETWEEN 100700 AND 100706 THEN 62
WHEN lg_032_ITEMS.CODE BETWEEN 100750 AND 100751 THEN 63
WHEN lg_032_ITEMS.CODE BETWEEN 100755 AND 100755 THEN 64
WHEN lg_032_ITEMS.CODE BETWEEN 100800 AND 100807 THEN 65
WHEN lg_032_ITEMS.CODE BETWEEN 100820 AND 100825 THEN 66
WHEN lg_032_ITEMS.CODE BETWEEN 100830 AND 100837 THEN 67
WHEN lg_032_ITEMS.CODE BETWEEN 100840 AND 100845 THEN 68
WHEN lg_032_ITEMS.CODE BETWEEN 100850 AND 100855 THEN 69
WHEN lg_032_ITEMS.CODE BETWEEN 100860 AND 100865 THEN 70
WHEN lg_032_ITEMS.CODE BETWEEN 100870 AND 100875 THEN 71
WHEN lg_032_ITEMS.CODE BETWEEN 100880 AND 100885 THEN 72
WHEN lg_032_ITEMS.CODE BETWEEN 100889 AND 100889 THEN 73
WHEN lg_032_ITEMS.CODE BETWEEN 100890 AND 100895 THEN 74
WHEN lg_032_ITEMS.CODE BETWEEN 100897 AND 100898 THEN 75
WHEN lg_032_ITEMS.CODE BETWEEN 100900 AND 100905 THEN 76
WHEN lg_032_ITEMS.CODE BETWEEN 100910 AND 100917 THEN 77
WHEN lg_032_ITEMS.CODE BETWEEN 100918 AND 100919 THEN 78
WHEN lg_032_ITEMS.CODE BETWEEN 100920 AND 100920 THEN 79
WHEN lg_032_ITEMS.CODE BETWEEN 100950 AND 100950 THEN 80
WHEN lg_032_ITEMS.CODE BETWEEN 100960 AND 100971 THEN 81
WHEN lg_032_ITEMS.CODE BETWEEN 100980 AND 100983 THEN 82
WHEN lg_032_ITEMS.CODE BETWEEN 101000 AND 101003 THEN 83
WHEN lg_032_ITEMS.CODE BETWEEN 101006 AND 101008 THEN 84
WHEN lg_032_ITEMS.CODE IN ('101010','101011','101012','10101201','101013','101014','101015','101016') THEN 85
WHEN lg_032_ITEMS.CODE BETWEEN 101020 AND 101023 THEN 86
WHEN lg_032_ITEMS.CODE BETWEEN 105050 AND 105055 THEN 87
WHEN lg_032_ITEMS.CODE BETWEEN 106001 AND 106002 THEN 88
WHEN lg_032_ITEMS.CODE BETWEEN 106005 AND 106005 THEN 89
WHEN lg_032_ITEMS.CODE BETWEEN 106010 AND 106011 THEN 90
WHEN lg_032_ITEMS.CODE BETWEEN 106015 AND 106015 THEN 91
WHEN lg_032_ITEMS.CODE BETWEEN 106020 AND 106023 THEN 92
WHEN lg_032_ITEMS.CODE BETWEEN 106024 AND 106024 THEN 93
WHEN lg_032_ITEMS.CODE BETWEEN 106025 AND 106029 THEN 94
WHEN lg_032_ITEMS.CODE BETWEEN 106029 AND 106033 THEN 95
WHEN lg_032_ITEMS.CODE BETWEEN 106034 AND 106035 THEN 96
WHEN lg_032_ITEMS.CODE BETWEEN 106036 AND 106036 THEN 97
WHEN lg_032_ITEMS.CODE BETWEEN 106037 AND 106038 THEN 98
WHEN lg_032_ITEMS.CODE BETWEEN 106039 AND 106039 THEN 99
WHEN ((lg_032_ITEMS.CODE BETWEEN 106050 AND 106054) OR (lg_032_ITEMS.CODE BETWEEN 100660 AND 100664)) THEN 100
WHEN lg_032_ITEMS.CODE IN('106065','10606501','10606510','10606520','106066','106067') THEN 101
WHEN lg_032_ITEMS.CODE IN('106070','106071','106073') THEN 102
WHEN lg_032_ITEMS.CODE IN('106074','10607401','10607420','106075','106076') THEN 103
END AS SIRALAMA

FROM  LG_032_01_INVOICE (NOLOCK)
INNER JOIN  LG_032_CLCARD (NOLOCK) ON ( LG_032_01_INVOICE.CLIENTREF =  LG_032_CLCARD.LOGICALREF)
LEFT OUTER JOIN LG_SLSMAN (NOLOCK) ON ( LG_032_01_INVOICE.SALESMANREF = LG_SLSMAN.LOGICALREF)
INNER JOIN  LG_032_01_STLINE (NOLOCK) ON ( LG_032_01_INVOICE.LOGICALREF =  LG_032_01_STLINE.INVOICEREF)
INNER JOIN  LG_032_ITEMS (NOLOCK) ON ( LG_032_01_STLINE.STOCKREF =  LG_032_ITEMS.LOGICALREF)
--LEFT OUTER JOIN  LG_031_ITEMS (NOLOCK) ON ( LG_032_01_STLINE.STOCKREF =  LG_031_ITEMS.LOGICALREF)
LEFT OUTER JOIN LG_032_ITMUNITA (NOLOCK) ON (LG_032_ITEMS.LOGICALREF= LG_032_ITMUNITA.ITEMREF)

WHERE
LG_032_01_INVOICE.TRCODE IN (3,8)
AND LG_032_01_STLINE.LINETYPE IN (0,1)
AND  LG_032_01_INVOICE.CANCELLED=0
AND LG_032_ITEMS.CODE LIKE '1%'
--AND (LG_032_ITEMS.CODE LIKE '1%' OR LG_032_ITEMS.CODE LIKE '00001' OR LG_032_ITEMS.CODE LIKE 'D2093' OR LG_032_ITEMS.CODE LIKE 'D2094' OR LG_032_ITEMS.CODE LIKE 'D2019' OR LG_032_ITEMS.CODE LIKE 'A30004')
AND LG_032_ITEMS.CODE NOT IN ('100000','1006790','106062')
--BURADA DÖKME ÜRÜNLER VAR.

AND LG_032_ITMUNITA.CONVFACT1='1' --koli litre değeri




Her türlü mantığı denedim ama satılmayan ürünleri çekemedim db'den, kesin biyerde bir yanlışım hatam var ama
çözemiyorum hocam..

[SertacSolmaz]

Hocam Kodum şu hale geldi sen son :

Kod: [Seç]

SELECT     
IT.LOGICALREF,IT.CODE,IT.NAME,CL.LOGICALREF,
CL.CODE,CL.DEFINITION_,CL.SPECODE,CL.CARDTYPE

FROM  LG_032_ITEMS as IT (NOLOCK)
inner join LG_032_CLCARD as CL (NOLOCK)  on (CL.LOGICALREF = IT.LOGICALREF)

WHERE
(IT.LOGICALREF not in
(SELECT ST.STOCKREF FROM LG_032_01_STLINE as ST (NOLOCK)
 WHERE ST.LINETYPE IN (0,1)
--and   day(ST.DATE_) = 1
--and   month(ST.DATE_) = 2
and   year(ST.DATE_) = 2012
AND ST.INVOICEREF like '%')
)

and IT.CODE like '1%'
and CL.CODE like 'MS 3103'
ORDER BY CL.CODE

Şöyle bir durum var, her bayi için sadece tek kayıt geliyor.
Bayileri denedikce, ya 0 sonuç dönüyor, yada 1..
Subquery'den kaynaklanıyor sanırım bu durumda..

[MehmetYavuz]

BU İKİ TABLO BÖYLE BAĞLANMAZ!

FROM  LG_032_ITEMS as IT (NOLOCK)
inner join LG_032_CLCARD as CL (NOLOCK)  on (CL.LOGICALREF = IT.LOGICALREF)

bu kodu kullanmak zorundasınız. fiş türü eklemek zorundasınız, en azından devir hariç demeniz gerekiyor. deviri de dahil ederseniz, ürün zaten hareket görmüş oluyor.

baktın hala olmuyo, para sıkışmıştır araya. bi uzmandan yardım al.

SELECT   CLCARD.CODE, CLCARD.DEFINITION_, ITEMS.CODE, ITEMS.NAME
FROM   LG_001_ITEMS ITEMS (NOLOCK), LG_001_CLCARD CLCARD
WHERE   CLCARD.CODE+'_'+ITEMS.CODE NOT IN (
   SELECT   CLCARD.CODE+'_'+ITEMS.CODE
   FROM   LG_001_01_STLINE STLINE (NOLOCK)
      LEFT JOIN LG_001_ITEMS ITEMS (NOLOCK) ON ITEMS.LOGICALREF=STLINE.STOCKREF
      LEFT JOIN LG_001_CLCARD CLCARD (NOLOCK) ON CLCARD.LOGICALREF=STLINE.CLIENTREF
   WHERE   STLINE.CANCELLED=0
         AND STLINE.INVOCIEREF<>0
         AND STLINE.LINETYPE IN (0,1)
         AND CLCARD.CODE LIKE '120.01%'
         AND YEAR(STLINE.DATE_)=2012
         AND CLCARD.CODE LIKE 'MS 3103'
         AND STLINE.TRCODE IN ( 8 )
   GROUP BY CLCARD.CODE+'_'+ITEMS.CODE)
WHERE CLCARD.CODE LIKE 'MS 3103'
ORDER BY CLCARD.CODE,ITEMS.CODE

[SertacSolmaz]

BU İKİ TABLO BÖYLE BAĞLANMAZ!

FROM  LG_032_ITEMS as IT (NOLOCK)
inner join LG_032_CLCARD as CL (NOLOCK)  on (CL.LOGICALREF = IT.LOGICALREF)

bu kodu kullanmak zorundasınız. fiş türü eklemek zorundasınız, en azından devir hariç demeniz gerekiyor. deviri de dahil ederseniz, ürün zaten hareket görmüş oluyor.

baktın hala olmuyo, para sıkışmıştır araya. bi uzmandan yardım al.

SELECT   CLCARD.CODE, CLCARD.DEFINITION_, ITEMS.CODE, ITEMS.NAME
FROM   LG_001_ITEMS ITEMS (NOLOCK), LG_001_CLCARD CLCARD
WHERE   CLCARD.CODE+'_'+ITEMS.CODE NOT IN (
   SELECT   CLCARD.CODE+'_'+ITEMS.CODE
   FROM   LG_001_01_STLINE STLINE (NOLOCK)
      LEFT JOIN LG_001_ITEMS ITEMS (NOLOCK) ON ITEMS.LOGICALREF=STLINE.STOCKREF
      LEFT JOIN LG_001_CLCARD CLCARD (NOLOCK) ON CLCARD.LOGICALREF=STLINE.CLIENTREF
   WHERE   STLINE.CANCELLED=0
         AND STLINE.INVOCIEREF<>0
         AND STLINE.LINETYPE IN (0,1)
         AND CLCARD.CODE LIKE '120.01%'
         AND YEAR(STLINE.DATE_)=2012
         AND CLCARD.CODE LIKE 'MS 3103'
         AND STLINE.TRCODE IN ( 8 )
   GROUP BY CLCARD.CODE+'_'+ITEMS.CODE)
WHERE CLCARD.CODE LIKE 'MS 3103'
ORDER BY CLCARD.CODE,ITEMS.CODE

Hocam 9. satırdaki, INVOCIERE'i  INVOICEREF'le  replace edip,
16. satırdaki where yerine and yazdım.

Kodunuz mükemmel olarak çalıştı
Ellerinize sağlık, Allah Razı olsun...


Not: Ihtiyacı olanların kullanabilmeleri için, yaptığım değişiklikleri düzenleme ile bildirdim.