Lütfen Query de yardım edin.

[Melik]

Merhaba Arkadaşlar. Bi Query benim 3 gündür huzurumu aldı.
Bilgisi olan arkadaşlardan yardım etmelelrini rica ediyorum

Böyle bir Sorğu yazdım

Kod: [Seç]

SELECT LG_020_ITEMS.CODE AS [Malzeme Kodu],LG_020_05_STLINE.STOCKREF,
LG_020_ITEMS.NAME3 AS [Malzeme adı2],
ISNULL((SELECT SUM(AMOUNT) FROM LG_020_04_STLINE WHERE STOCKREF = LG_020_ITEMS.LOGICALREF AND TRCODE IN(7,8)AND YEAR(DATE_) = 2017 ),0)AS [2017_Satış],
ISNULL((SELECT SUM(AMOUNT) FROM LG_020_05_STLINE WHERE STOCKREF = LG_020_ITEMS.LOGICALREF AND TRCODE IN(7,8)AND YEAR(DATE_) = 2018 ),0)AS [2017_Satış],
LV_020_05_GNTOTST.ONHAND AS [STok],LV_020_05_GNTOTST.ACTSORDER AS [Bekleyen_Verilen_Sipraişler], Lv_020_05_GNTOTST.RECEIVED+ Lv_020_05_GNTOTST.ACTPORDER as [Toplam Verilen Sipariş],
CASE
WHEN (Lv_020_05_GNTOTST.RECEIVED+ Lv_020_05_GNTOTST.ACTPORDER-(sum(LG_020_05_STLINE.AMOUNT) )<1) THEN '0'
WHEN (Lv_020_05_GNTOTST.RECEIVED+ Lv_020_05_GNTOTST.ACTPORDER-(sum(LG_020_05_STLINE.AMOUNT) ) >1 ) THEN  Lv_020_05_GNTOTST.RECEIVED + Lv_020_05_GNTOTST.ACTPORDER -(sum(LG_020_05_STLINE.AMOUNT) )
END [Bekleyen_Veien sipariş],
CASE
WHEN LG_020_05_STLINE.STATUS = 1 THEN (SELECT  SUM(LG_020_05_STLINE.AMOUNT) FROM LG_020_05_STLINE WHERE  TRCODE=1 AND LG_020_05_STLINE.STATUS=1)
WHEN LG_020_05_STLINE.STATUS = 0 THEN 0
END [Sevk Elilen sipariş],
''as [Not],
Lv_020_05_GNTOTST.RECEIVED + Lv_020_05_GNTOTST.ACTPORDER -(sum(LG_020_05_STLINE.AMOUNT) ) AS [INFO1],
LG_020_05_STLINE.STATUS AS [INFO2],
COUNT(LG_020_05_STLINE.AMOUNT) AS [INFO3],
sum(LG_020_05_ORFLINE.SHIPPEDAMOUNT) AS [INFO4]
 FROM  LG_020_ITEMS 
 LEFT OUTER JOIN LV_020_05_GNTOTST ON LG_020_ITEMS.LOGICALREF = LV_020_05_GNTOTST.STOCKREF
 LEFT OUTER JOIN LG_020_05_STLINE ON LG_020_05_STLINE.STOCKREF = LG_020_ITEMS.LOGICALREF
 LEFT OUTER JOIN LG_020_05_ORFLINE ON LG_020_ITEMS.LOGICALREF = LG_020_05_ORFLINE.STOCKREF
 WHERE (LV_020_05_GNTOTST.INVENNO = -1) AND LG_020_ITEMS.CYPHCODE='Yetki_2'   
 AND  LG_020_05_ORFLINE.CLOSED=0   AND  LG_020_05_ORFLINE.TRCODE ='2' AND LG_020_05_STLINE.TRCODE=1 and LG_020_ITEMS.CODE='LF 17509'

 GROUP BY LG_020_ITEMS.CODE,LG_020_ITEMS.NAME,LG_020_ITEMS.NAME3, LG_020_05_STLINE.STOCKREF,LV_020_05_GNTOTST.STOCKREF,LG_020_ITEMS.LOGICALREF,
 LV_020_05_GNTOTST.ONHAND, LV_020_05_GNTOTST.ACTSORDER,Lv_020_05_GNTOTST.RECEIVED,LV_020_05_GNTOTST.ACTPORDER ,
 LG_020_05_ORFLINE.SHIPPEDAMOUNT,LG_020_05_STLINE.STATUS

Sonuc


Yapmaya çalışdığım şu ki. stockrefler tekrarlanmasın.
Ama yine tekrarlanıyor....

[hyponectia]

Merhabalar,

DISTINCT ekleyip, tekrardan deneyebilir misiniz?

[vedat_ozer]

LG_006_01_ORFLINE.SHIPPEDAMOUNT alanını hem sum() yapmışınız hemde group by eklemişiniz.

[Sofu Ahmed]

Merhaba,


LG_006_01_ORFLINE.SHIPPEDAMOUNT bu alanı grup by dan kaldırın