SQL İLE Bir Faturaın Dokumnu Nasıl ALRIM

[yousemeti]

selam degerli  hocalarım ;

ben bir faturanın dokumunu almak istiyorum sql ile
item,stline,clcard,invoice bu tablaloradan alınıyor bildigim kararıyla
ama beceremedim  yapamak istedigim sey fatura numarsı 111111 olan faturanın içini dokmek yani hangi stokları almıs kac adet almıs ve ne kadar tutmus  yardım ederseniz  cok ama cok sevinirim

saygılarımla
salih hanifeoglu
 

[Monsieur®]

   SELECT  INVOICE.DATE_ AS [FATURA TARIHI],
      INVOICE.FICHENO AS [FATURA NO],
      CLCARD.CODE AS [CARI HESAP KODU],
      CLCARD.DEFINITION_ AS [CARI HESAP AÇIKLAMASI],
      ITEMS.CODE  AS [ÜRÜN KODU],
      ITEMS.NAME AS [ÜRÜN AÇIKLAMASI],
      UNIT.NAME AS [BİRİMİ],
      STLINE.AMOUNT AS [MİKTARI],
      STLINE.PRICE AS [BİRİM FİYATI],
      (STLINE.VATAMNT+STLINE.VATMATRAH) AS [SATIR TUTARI]
   FROM    LG_999_01_INVOICE INVOICE (NOLOCK) 
      LEFT OUTER JOIN LG_999_01_STLINE STLINE
         (NOLOCK) ON INVOICE.LOGICALREF = STLINE.INVOICEREF   
      LEFT OUTER JOIN LG_999_CLCARD CLCARD
         (NOLOCK) ON INVOICE.CLIENTREF = CLCARD.LOGICALREF   
      LEFT OUTER JOIN LG_999_ITEMS ITEMS
         (NOLOCK) ON STLINE.STOCKREF = ITEMS.LOGICALREF 
      LEFT OUTER JOIN LG_999_UNITSETL UNIT
         (NOLOCK) ON STLINE.UOMREF = UNIT.LOGICALREF
   WHERE    INVOICE.FICHENO='111111'




SANIRIM BU İŞİNİ GÖRÜR

[yousemeti]

hocam cok ama cok saol oldu valla işime yaradı cok teşekür ederim gercekten

ellerin dert gorumesin
kolay gelsin

salih hanifeoglu

[Monsieur®]

Bir şey deil iki üç dk sürdü zaten
 

[yousemeti]

bende finalini gonderiyourm

borc - alacak = bakiye  veren sql gule gule kullanın en  cok lazım olanalardan brisidir heralde
mesela bana  cok lazım oluyor  boyle  seyler raporlama yaparken

SELECT     LG_105_CLCARD.CODE AS [Cari.Kod], LG_105_CLCARD.DEFINITION_ AS [Cari Hesap], LG_105_01_GNTOTCL.DEBIT AS Borç,
                      LG_105_01_GNTOTCL.CREDIT AS Alaçak, LG_105_01_GNTOTCL.DEBIT - LG_105_01_GNTOTCL.CREDIT AS Bakiye
FROM         LG_105_CLCARD INNER JOIN
                      LG_105_01_GNTOTCL ON LG_105_CLCARD.LOGICALREF = LG_105_01_GNTOTCL.CARDREF
WHERE     (LG_105_01_GNTOTCL.TOTTYP = 1) AND (LG_105_CLCARD.CODE = '120.1681')

[CebrailEREN]

Merhabalar Monsieur®:

Kodlar çok güzel, Henuz faturalanmamış irsaliyeleri de hesaplasaydı çok daha iyi olurdu

[haskan6961]

Kod: [Seç]

SELECT  INV.DATE_ AS [FATURA TARIHI],INV.FICHENO AS [FATURA NO],
      CLC.CODE AS [CARI HESAP KODU],CLC.DEFINITION_ AS [CARI HESAP AÇIKLAMASI],
      STF.FICHENO AS [İRS.NO],STF.DATE_ AS [İRS.TARİHİ],
  ITM.CODE  AS [ÜRÜN KODU],ITM.NAME AS [ÜRÜN AÇIKLAMASI],
      UNIT.NAME AS [BİRİMİ],
      STL.AMOUNT AS [MİKTARI],STL.PRICE AS [BİRİM FİYATI],
      (STL.VATAMNT+STL.VATMATRAH) AS [SATIR TUTARI]
 
   FROM    LG_506_01_INVOICE INV   
      RIGHT OUTER JOIN LG_506_01_STFICHE STF ON INV.LOGICALREF = STF.INVOICEREF   
      LEFT OUTER JOIN LG_506_01_STLINE STL ON STF.LOGICALREF = STL.STFICHEREF   
  LEFT OUTER JOIN LG_506_CLCARD CLC ON INV.CLIENTREF = CLC.LOGICALREF   
      LEFT OUTER JOIN LG_506_ITEMS ITM ON STL.STOCKREF = ITM.LOGICALREF 
      LEFT OUTER JOIN LG_506_UNITSETL UNIT ON STL.UOMREF = UNIT.LOGICALREF
   WHERE    --INV.FICHENO='00022'
   CLC.LOGICALREF=681
   UNION
SELECT  DISTINCT '' AS [FATURA TARIHI],'' AS [FATURA NO],
      CLC.CODE AS [CARI HESAP KODU],CLC.DEFINITION_ AS [CARI HESAP AÇIKLAMASI],
      STF.FICHENO AS [İRS.NO],STF.DATE_ AS [İRS.TARİHİ],
  ITM.CODE  AS [ÜRÜN KODU],ITM.NAME AS [ÜRÜN AÇIKLAMASI],
      UNIT.NAME AS [BİRİMİ],
      STL.AMOUNT AS [MİKTARI],STL.PRICE AS [BİRİM FİYATI],
      (STL.VATAMNT+STL.VATMATRAH) AS [SATIR TUTARI]
 
   FROM   LG_506_01_STFICHE STF
      LEFT OUTER JOIN LG_506_01_STLINE STL ON STF.LOGICALREF = STL.STFICHEREF   
  LEFT OUTER JOIN LG_506_CLCARD CLC ON STF.CLIENTREF = CLC.LOGICALREF   
      LEFT OUTER JOIN LG_506_ITEMS ITM ON STL.STOCKREF = ITM.LOGICALREF 
      LEFT OUTER JOIN LG_506_UNITSETL UNIT ON STL.UOMREF = UNIT.LOGICALREF
   WHERE   STF.BILLED=0 AND CLC.LOGICALREF=681
   ORDER BY STF.DATE_