Satış ile Sipariş fişlerin satır no ları hkk.

[serkana]

Merhaba Arkadaşlar,

bızde faturaların hepsı sıparıslerden cekıp aktarılıyor bunun ıcın bır sorgu yazdım ve yapmak ıstedıgım faturadakı satır ve sıparıstekı satırı getırmek.
fatura satırlarda sorun yok ama sıparıs, yani orfiche ve orfline, tablolarını ekledıgım zaman yanlıs cıkıyor sorgu.

yardımlarınızı rıca edıyorum.

serkan

sql:

SELECT TOP (100) PERCENT I.FICHENO, dbo.LG_041_01_STLINE.INVOICELNNO AS FAT_S_NO,
  (SELECT     NAME
  FROM          dbo.LG_041_ITEMS
  WHERE      (dbo.LG_041_01_STLINE.STOCKREF = LOGICALREF)) AS KOD,
  CASE WHEN I.TRCODE = 3 THEN 'N' WHEN I.TRCODE = 8 THEN 'M' WHEN LG_041_01_STLINE.LINETYPE = 4 THEN 'N' ELSE 'M' END AS FAT_TUR,
   I.DATE_ AS FAT_DATE, I.DATE_ AS FAT_TARIH,
  (SELECT     FICHENO
 FROM          dbo.LG_041_01_ORFICHE
 WHERE      (LOGICALREF = dbo.LG_041_01_STLINE.ORDFICHEREF)) AS SIP_NO, I.REPORTRATE AS RATE, dbo.LG_041_01_STLINE.AMOUNT,
 dbo.LG_041_01_STLINE.PRICE / I.REPORTRATE AS ADET_EUR, dbo.LG_041_01_STLINE.TOTAL / I.REPORTRATE AS EUR,
I.GROSSTOTAL / I.REPORTRATE AS EUR_TOT
FROM   dbo.LG_041_01_INVOICE AS I LEFT OUTER JOIN
 dbo.LG_041_01_STLINE ON dbo.LG_041_01_STLINE.INVOICEREF = I.LOGICALREF LEFT OUTER JOIN
 dbo.LG_041_01_ORFICHE AS LG_041_01_ORFICHE_1 ON LG_041_01_ORFICHE_1.LOGICALREF = dbo.LG_041_01_STLINE.ORDFICHEREF LEFT OUTER JOIN
dbo.LG_041_SRVCARD AS LG_041_SRVCARD_1 ON LG_041_SRVCARD_1.LOGICALREF = dbo.LG_041_01_STLINE.STOCKREF
WHERE     (I.TRCODE IN (3, 8 )) AND (I.DATE_ BETWEEN CONVERT(DATETIME, '2012-03-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-03-31 00:00:00', 102)) AND
 (dbo.LG_041_01_STLINE.CANCELLED = 0) AND (dbo.LG_041_01_STLINE.LINETYPE IN (0, 4)) AND (dbo.LG_041_01_STLINE.STOCKREF NOT LIKE
  (SELECT     LOGICALREF
 FROM          dbo.LG_041_SRVCARD
WHERE      (DEFINITION_ = 'NAKLİYE'))) AND (dbo.LG_041_01_STLINE.RETCOSTTYPE = 0)

[zamzam]

SELECT LG_xxx_ITEMS.STGRPCODE AS 'Hiyerarşi', LG_xxx_xx_ORFICHE.DATE_ AS 'Sip.Tarihi', LG_xxx_xx_ORFICHE.FICHENO AS 'Sip.Fiş No', LG_xxx_xx_INVOICE.DATE_ AS 'Fat.Tarihi', LG_xxx_xx_INVOICE.FICHENO AS 'Fat.Fiş No:', LG_xxx_CLCARD.CODE AS 'UL', LG_xxx_CLCARD.DEFINITION_ AS 'ÜNVAN', LG_xxx_CLCARD.TRADINGGRP AS 'Prom.Snf.', LG_xxx_ITEMS.CODE AS 'KOD', LG_xxx_ITEMS.NAME AS 'MAMUL', LG_xxx_xx_ORFLINE.AMOUNT AS 'Sip.Mik.AD.', LG_xxx_xx_STLINE.AMOUNT AS 'Fat. Mik. AD.', LG_xxx_UNITSETL.CODE AS 'Birim', LG_xxx_xx_STLINE.LINENET AS 'NSV', LG_xxx_xx_STLINE.TOTAL AS 'GSV'
FROM UNITY.dbo.LG_xxx_xx_INVOICE LG_xxx_xx_INVOICE, UNITY.dbo.LG_xxx_xx_ORFICHE LG_xxx_xx_ORFICHE, UNITY.dbo.LG_xxx_xx_ORFLINE LG_xxx_xx_ORFLINE, UNITY.dbo.LG_xxx_xx_STLINE LG_xxx_xx_STLINE, UNITY.dbo.LG_xxx_CLCARD LG_xxx_CLCARD, UNITY.dbo.LG_xxx_ITEMS LG_xxx_ITEMS, UNITY.dbo.LG_xxx_UNITSETF LG_xxx_UNITSETF, UNITY.dbo.LG_xxx_UNITSETL LG_xxx_UNITSETL
WHERE LG_xxx_xx_STLINE.STOCKREF = LG_xxx_ITEMS.LOGICALREF AND LG_xxx_xx_STLINE.INVOICEREF = LG_xxx_xx_INVOICE.LOGICALREF AND LG_xxx_xx_STLINE.ORDFICHEREF = LG_xxx_xx_ORFICHE.LOGICALREF AND LG_xxx_xx_STLINE.ORDTRANSREF = LG_xxx_xx_ORFLINE.LOGICALREF AND LG_xxx_xx_STLINE.CLIENTREF = LG_xxx_CLCARD.LOGICALREF AND LG_xxx_UNITSETL.UNITSETREF = LG_xxx_UNITSETF.LOGICALREF AND LG_xxx_xx_ORFLINE.UOMREF = LG_xxx_UNITSETL.LOGICALREF AND LG_xxx_xx_ORFLINE.USREF = LG_xxx_UNITSETF.LOGICALREF AND ((LG_xxx_xx_STLINE.SOURCEINDEX=0 And LG_xxx_xx_STLINE.SOURCEINDEX=0) AND (LG_xxx_CLCARD.ACTIVE=0) AND (LG_xxx_ITEMS.ACTIVE=0) AND (LG_xxx_xx_STLINE.LINETYPE In (0,6,1)) AND (LG_xxx_xx_ORFICHE.TRCODE=1) AND (LG_xxx_xx_INVOICE.CANCELLED=0) AND (LG_xxx_xx_STLINE.CANCELLED=0) AND (LG_xxx_xx_INVOICE.GRPCODE=2) AND (LG_xxx_xx_INVOICE.TRCODE=8) AND (LG_xxx_xx_ORFICHE.DATE_ Between {ts '2012-01-01 00:00:00'} And {ts '2012-12-31 00:00:00'}))
ORDER BY LG_xxx_xx_INVOICE.DATE_, LG_xxx_xx_ORFICHE.FICHENO

  Benim sipariş ve faturaları ortak kullandığım sorgum işinize yarar umarım.

[serkana]

teşekkürker,

işime yaradı ve sizin scripten nasıl ilerlemem gerekiyor diye çözdüm. 

hayırlı akşamlar.

serkan