Aşağıdaki sorguya satış irsaliyelerini de ekleyebilir miyiz?
SELECT YEAR(INV.DATE_) YIL,MONTH(INV.DATE_)AY,
"FIS TURU"=CASE WHEN STL.TRCODE IN (1) THEN 'ALIM'
WHEN STL.TRCODE IN (6) THEN 'ALIM ÝADE' END,
AY=CASE MONTH(STL.DATE_)
WHEN 1 THEN '01.OCAK'
WHEN 2 THEN '02.ŞUBAT'
WHEN 3 THEN '03.MART'
WHEN 4 THEN '04.NİSAN'
WHEN 5 THEN '05.MAYIS'
WHEN 6 THEN '06.HAZİRAN'
WHEN 7 THEN '07.TEMMUZ'
WHEN 8 THEN '08.AĞUSTOS'
WHEN 9 THEN '09.EYLÜL'
WHEN 10 THEN '10.EKİM'
WHEN 11 THEN '11.KASIM'
WHEN 12 THEN '12.ARALIK'
ELSE 'DÝÐER'
END ,
"FATURA NO"=INV.FICHENO,"FATURA TARIHI"=INV.DATE_,
"IRSALIYE NO"=STF.FICHENO,"IRSALIYE TARIHI"=STL.DATE_,
"SATICI KODU"=CLC.CODE,"SATICI ADI"=CLC.DEFINITION_,
"MALZEME KODU"=ITM.CODE,"MALZEME ACIKLAMA"=ITM.NAME,
"MALZEME OK1"=ITM.SPECODE,"MALZEME OK2"=ITM.SPECODE2,"MALZEME OK3"=ITM.SPECODE3,"MALZEME OK4"=ITM.SPECODE4,"MALZEME OK5"=ITM.SPECODE5,
"MALZEME GRUP KODU"=ITM.STGRPCODE,"MALZEME YETKI KODU"=ITM.CYPHCODE,
"MIKTAR"=CASE WHEN STL.TRCODE IN (1) THEN AMOUNT ELSE AMOUNT*-1 END,
"BIRIM"=UNI.CODE,
"FIYAT"=STL.PRICE,
"TUTAR"=CASE WHEN STL.TRCODE IN (1) THEN STL.TOTAL ELSE STL.TOTAL*-1 END,
"NET TUTAR"=CASE WHEN STL.TRCODE IN (1) THEN STL.LINENET ELSE STL.LINENET*-1 END,
"NET FIYAT"=CASE WHEN STL.TRCODE IN (1) THEN STL.LINENET/STL.AMOUNT ELSE STL.LINENET/STL.AMOUNT END,
"FD FIYAT"= CASE WHEN PRRATE<>0 THEN((LINENET/AMOUNT)/PRRATE) ELSE '' END,
"FD DOVIZ TURU"=CASE PRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD'WHEN 20 THEN 'EUR' WHEN 160 THEN 'TL' ELSE '' END,
"SON FD FIYAT"= (SELECT TOP 1 CASE WHEN PRRATE<>0 THEN (LINENET/AMOUNT)/PRRATE ELSE '' END FROM LG_020_04_STLINE STL2 WHERE STL2.LINETYPE=0 AND STL2.STOCKREF=STL.STOCKREF AND STL2.CLIENTREF=STL.CLIENTREF
AND STL2.LINENET<>0 AND TRCODE=1 ORDER BY STL2.DATE_ DESC),
"SON FD DOVIZ TURU"=CASE (SELECT TOP 1 PRCURR FROM LG_020_04_STLINE STL2 WHERE STL2.LINETYPE=0 AND STL2.STOCKREF=STL.STOCKREF AND STL2.CLIENTREF=STL.CLIENTREF
AND STL2.LINENET<>0 AND TRCODE=1 ORDER BY STL2.DATE_ DESC)
WHEN 0 THEN 'TL' WHEN 1 THEN 'USD'WHEN 20 THEN 'EUR' WHEN 160 THEN 'TL' ELSE '' END,
PRRATE,
"LISTE FIYAT"=ISNULL((SELECT TOP 1 PRICE FROM LG_020_PRCLIST PRC WHERE PTYPE=1 AND PRC.CARDREF=STL.STOCKREF AND STL.DATE_ BETWEEN PRC.BEGDATE AND PRC.ENDDATE
AND PRC.CURRENCY=STL.TRCURR ),0),
"LISTE TUTAR"=ISNULL((SELECT TOP 1 PRICE FROM LG_020_PRCLIST PRC WHERE PTYPE=1 AND PRC.CARDREF=STL.STOCKREF AND STL.DATE_ BETWEEN PRC.BEGDATE AND PRC.ENDDATE
AND PRC.CURRENCY=STL.TRCURR ),0)*STL.AMOUNT,
"AMBAR NO"=STL.SOURCEINDEX,"AMBAR ADI"=WHO.NAME,
"ISLEM DOVIZI"=CASE STL.TRCURR WHEN 1 THEN 'USD' WHEN 20 THEN 'EUR' ELSE 'TL' END,
"ID NET TUTAR"=CASE WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE <>0 THEN STL.LINENET/STL.TRRATE
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE <>0 THEN (STL.LINENET*-1)/STL.TRRATE
WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE =0 THEN STL.LINENET
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE =0 THEN (STL.LINENET*-1) END,
"ID NET MALIYET"=CASE WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE <>0 THEN (STL.OUTCOST*STL.AMOUNT)/STL.TRRATE
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE <>0 THEN ((STL.OUTCOST*STL.AMOUNT)*-1)/STL.TRRATE
WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE =0 THEN STL.OUTCOST*STL.AMOUNT
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE =0 THEN ((STL.OUTCOST*STL.AMOUNT)*-1) END
FROM LG_020_04_STLINE STL
LEFT JOIN LG_020_ITEMS ITM ON ITM.LOGICALREF=STL.STOCKREF
LEFT JOIN LG_020_04_INVOICE INV ON INV.LOGICALREF=STL.INVOICEREF
LEFT JOIN LG_020_04_STFICHE STF ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN LG_020_CLCARD CLC ON CLC.LOGICALREF=STL.CLIENTREF
LEFT JOIN LG_SLSMAN SLS ON SLS.LOGICALREF=STL.SALESMANREF
LEFT JOIN LG_020_UNITSETL UNI ON UNI.LOGICALREF=STL.UOMREF
LEFT JOIN L_CAPIWHOUSE WHO ON WHO.NR=STL.SOURCEINDEX AND WHO.FIRMNR=211
WHERE STL.TRCODE IN (1,6) AND LINETYPE IN (0,1) AND LINENET<>0