merhaba ersin ayas
asagidaki sorgu kullanarak istedigini yapabilirsin (group by yaparak)
Sorgu1
DECLARE @Tar1 DATETIME
DECLARE @Tar2 DATETIME
SET @Tar1 ='01.01.2012'
SET @Tar2 ='01.31.2015'
SELECT
STL.TRCODE,
(CASE STL.TRCODE
WHEN 1 THEN '(01) Satınalma İrsaliyesi'
WHEN 2 THEN '(02) Perakende Satış İade İrsaliyesi'
WHEN 3 THEN '(03) Toptan Satış İade İrsaliyesi'
WHEN 4 THEN '(04) Konsinye Çıkış İade İrsaliyesi'
WHEN 5 THEN '(05) Konsinye Giriş İrsaliyesi'
WHEN 13 THEN '(13) Üretimden Giriş Fişi'
WHEN 14 THEN '(14) Devir Fişi'
WHEN 50 THEN '(50) Sayım Fazlası Fişi'
WHEN 15 THEN '(15) Tanımlı Malzeme Giriş Fişi'
WHEN 16 THEN '(16) Tanımlı Malzeme Giriş Fişi'
WHEN 17 THEN '(17) Tanımlı Malzeme Giriş Fişi'
WHEN 18 THEN '(18) Tanımlı Malzeme Giriş Fişi'
WHEN 19 THEN '(19) Tanımlı Malzeme Giriş Fişi'
WHEN 30 THEN '(30) Tanımlı Satınalma İrsaliyesi'
WHEN 31 THEN '(31) Tanımlı Satınalma İrsaliyesi'
WHEN 32 THEN '(32) Tanımlı Satınalma İrsaliyesi'
WHEN 33 THEN '(33) Tanımlı Satınalma İrsaliyesi'
WHEN 34 THEN '(34) Tanımlı Satınalma İrsaliyesi'
WHEN 6 THEN '(06) Satınalma İade İrsaliyesi'
WHEN 7 THEN '(07) Perakende Satış İrsaliyesi'
WHEN 8 THEN '(08) Toptan Satış İrsaliyesi'
WHEN 9 THEN '(09) Konsinye Çıkış İrsaliyesi'
WHEN 11 THEN '(11) Fire Fişi'
WHEN 12 THEN '(11) Sarf Fişi'
WHEN 51 THEN '(11) Sayım Eksiği Fişi'
WHEN 20 THEN '(20) Tanımlı Malzeme Çıkış Fişi'
WHEN 21 THEN '(21) Tanımlı Malzeme Çıkış Fişi'
WHEN 22 THEN '(22) Tanımlı Malzeme Çıkış Fişi'
WHEN 23 THEN '(23) Tanımlı Malzeme Çıkış Fişi'
WHEN 24 THEN '(24) Tanımlı Malzeme Çıkış Fişi'
WHEN 35 THEN '(35) Tanımlı Satış İrsaliyesi'
WHEN 36 THEN '(36) Tanımlı Satış İrsaliyesi'
WHEN 37 THEN '(37) Tanımlı Satış İrsaliyesi'
WHEN 38 THEN '(38) Tanımlı Satış İrsaliyesi'
WHEN 39 THEN '(39) Tanımlı Satış İrsaliyesi'
ELSE 'ZZ' END ) AS FisTuru,
STL.BILLED,
STF.DATE_ AS IrsaliyeTarih,
STF.FICHENO AS IrsaliyeFisNumarasi,
STF.DOCODE AS IrsaliyeBelgeNo,
STF.TRACKNR AS IrsaliyeTakipNo,
STF.TRADINGGRP AS IrsaliyeTIG,
(CASE WHEN STL.STATUS=0 THEN 'Gerçek' ELSE CASE WHEN STL.STATUS=1 THEN 'Öneri' ELSE '' END END) AS Durumu,
(CASE WHEN STL.BILLED=1 THEN 'Evet' ELSE CASE WHEN STL.BILLED=0 THEN 'Hayır' ELSE '' END END) AS Faturalandirilmis,
INV.DATE_ AS FaturaTarih,
INV.SERIALCODE AS FaturaSeriNo,
INV.FICHENO AS FaturaFisNo,
INV.DOCODE AS FaturaBelgeNo,
(CASE WHEN INV.ACCOUNTED=1 THEN 'Evet' ELSE CASE WHEN INV.ACCOUNTED=0 THEN 'Hayır' ELSE '' END END) AS FaturaMuhasebelestirilmis,
(CASE WHEN (INV.ACCOUNTED=1 AND INV.ACCFICHEREF=0) THEN 'Evet' ELSE CASE WHEN (INV.ACCOUNTED=1 AND INV.ACCFICHEREF<>0) THEN 'Hayır' ELSE '' END END) AS MuhasebeFisiSorunlu,
EMF.FICHENO AS MuhasebeFisNo,
EMF.DOCODE AS MuhasebeBelgeNo,
EMF.JOURNALNO AS MuhasebeYevmiyeMaddeNo,
EMF.GENEXP1 AS MuhasebeFisiAciklama1,
EMF.GENEXP2 AS MuhasebeFisiAciklama2,
EMF.GENEXP3 AS MuhasebeFisiAciklama3,
EMF.GENEXP4 AS MuhasebeFisiAciklama4,
EMFL.KEBIRCODE AS MuhasebeFisSatiriKebirKod,
EMFL.ACCOUNTCODE AS MuhasebeFisSatiriHesapKod,
CLC.CODE AS CHKodu,
CLC.DEFINITION_ AS CHUnvani,
(SELECT CODE+'-'+DESCR FROM LG_015_MARK WHERE LOGICALREF=ITM.MARKREF) AS MalzemeMarka,
ITM.STGRPCODE AS MalzemeGrubu,
ITM.SPECODE AS MalzemeOzelKod1,
ITM.SPECODE2 AS MalzemeOzelKod2,
ITM.SPECODE3 AS MalzemeOzelKod3,
ITM.SPECODE4 AS MalzemeOzelKod4,
ITM.SPECODE5 AS MalzemeOzelKod5,
STL.STOCKREF AS MalzemeID,
ITM.CODE AS MalzemeKodu,
ITM.NAME AS MalzemeAdi,
STL.LINEEXP AS SatirAciklamasi,
(SELECT CODE+'-'+DEFINITION_ FROM LG_SLSMAN WHERE FIRMNR=015 AND ACTIVE=0 AND LOGICALREF=STL.SALESMANREF) AS SatisElemani,
(SELECT RIGHT(REPLICATE('0',3) + CONVERT(varchar(3),NR),3)+', '+NAME FROM L_CAPIWHOUSE CP WITH(NOLOCK) WHERE CP.FIRMNR=015 AND CP.NR=STL.SOURCEINDEX) AS SatirAmbar,
ROUND(ISNULL((CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.AMOUNT ELSE CONVERT(float,0) END),0),5) AS Miktar,
STL.UINFO1 AS Carpan1,
STL.UINFO2 AS Carpan2,
(SELECT CODE FROM LG_015_UNITSETL WHERE LOGICALREF=STL.UOMREF) AS AnaBirimi,
ROUND(ISNULL((CASE WHEN STL.AMOUNT<>0 THEN (CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.TOTAL/STL.AMOUNT ELSE CONVERT(float,0) END) ELSE CONVERT(float,0) END),0),5) AS BrutBirimFiyat,
ROUND(ISNULL((CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.TOTAL ELSE CONVERT(float,0) END),0),2) AS BrutSatirTutar,
ROUND(ISNULL((CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.DISTDISC ELSE CONVERT(float,0) END),0),2) AS IndirimTutar,
ROUND(ISNULL((CASE WHEN STL.AMOUNT<>0 THEN (CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.VATMATRAH/STL.AMOUNT ELSE CONVERT(float,0) END) ELSE CONVERT(float,0) END),0),5) AS NetBirimFiyat,
ROUND(ISNULL((CASE WHEN STL.IOCODE IN (1,2,3,4) THEN STL.VATMATRAH ELSE CONVERT(float,0) END),0),2) AS SatirTutar,
STL.VAT AS SatirKdvOrani,
ROUND(ISNULL(STL.VATAMNT,0),2) AS SatirKDVToplam,
ROUND(ISNULL(STL.VATAMNT + STL.VATMATRAH,0),2) AS SatirNetTutar
FROM LG_015_01_STLINE STL WITH(NOLOCK)
INNER JOIN LG_015_ITEMS ITM WITH(NOLOCK) ON ITM.LOGICALREF=STL.STOCKREF
INNER JOIN LG_015_CLCARD CLC WITH(NOLOCK) ON CLC.LOGICALREF=STL.CLIENTREF
LEFT OUTER JOIN LG_015_01_STFICHE STF ON STF.LOGICALREF=STL.STFICHEREF
LEFT OUTER JOIN LG_015_01_INVOICE INV ON INV.LOGICALREF=STL.INVOICEREF
LEFT OUTER JOIN LG_015_01_EMFICHE EMF ON INV.ACCFICHEREF=EMF.LOGICALREF
INNER JOIN LG_015_01_EMFLINE EMFL ON EMF.LOGICALREF=EMFL.ACCFICHEREF
WHERE
STL.TRCODE IN (1,2,3,4,5,13,14,15,16,17,18,19,30,31,32,33,34,50,6,7,8,9,11,12,20,21,22,23,24,35,36,37,38,39,51)
AND STL.BILLED IN (0,1)
AND STL.LINETYPE=0
AND STL.LPRODSTAT=0
AND STL.CANCELLED=0
AND STL.DATE_ BETWEEN @Tar1 AND @Tar2
ORDER BY STL.DATE_
