I need to get the EMISSION_DATE
field of TLX_VENDA
and the B9_DATA
field of the TLX_ESTOQUE_INI
table, however I need to do this within OUTER APPLY
.
I managed to get into the result but it is very slow.
Here is the last% wc% I made:
SELECT FN.COMPANY,
FN.FILIAL,
FN.DOC,
FN.SERIE,
FN.TIPONF,
NF.NFORI,
ESTORNO,
PRODUTO,
P.DESCR,
FN.LOCAL,
ISNULL (A2.A2_COD,A1_COD) [COD CLIENTE],
ISNULL (A2.A2_NOME,A1_NOME) [NOME CLIENTE],
-- ISNULL (A2.A2_NOME,A1_NOME) [NOME CLIENTE],
-- TM,
FN.ORIGEM,
NF.XXOPER,
-- QUANT,
FN.NUMSERI,
-- V.EMISSION_DATE,
-- E.B9_DATA
R.B9_DATA,
VE.EMISSION_DATE,
CAST(CONCAT(DATA, ' ', HRINI) AS DATETIME) DT,
-- DATAFIM,
CASE FN.FLAG_TM WHEN 'E' THEN 'ENTRADA' WHEN 'S' THEN 'SAIDA' END AS FLAG
FROM TLX_INVENTORY_TRANS_HIST FN
LEFT JOIN TLX_PRODUTOS_INFO P WITH (NOLOCK)
ON P.COD = FN.PRODUTO
LEFT JOIN TLX_NFI_ENTRADASAIDA NF WITH (NOLOCK)
ON NF.COMPANY = FN.COMPANY
AND NF.FILIAL = FN.FILIAL
AND NF.DOC = FN.DOC
AND NF.SERIE = FN.SERIE AND NF.NUMSEQ = FN.NUMSEQ
AND NF.COD = FN.PRODUTO AND NF.FLAG = FN.FLAG_TM
LEFT JOIN SA1010 A1 WITH (NOLOCK)
ON A1_COD = FN.CLIFOR
AND A1_LOJA = FN.LOJA
AND A1.D_E_L_E_T_ = ''
AND (
(FN.FLAG_TM = 'S' AND FN.TIPO NOT IN ('B', 'D'))
OR (FN.FLAG_TM = 'E' AND FN.TIPO IN ('B', 'D'))
)
LEFT JOIN SA2010 A2 WITH (NOLOCK)
ON A2_COD = FN.CLIFOR
AND A2_LOJA = FN.LOJA
AND A2.D_E_L_E_T_ = ''
AND (
(FN.FLAG_TM = 'S' AND FN.TIPO IN ('B', 'D'))
OR (FN.FLAG_TM = 'E' AND FN.TIPO NOT IN ('B', 'D'))
)
OUTER APPLY (
SELECT B9_COD,B9_DATA
FROM TLX_ESTOQUE_INI AS E
WHERE E.COMPANY = FN.COMPANY
AND E.B9_FILIAL = FN.FILIAL
AND E.B9_COD = FN.PRODUTO
) AS R
OUTER APPLY (
SELECT EMISSION_DATE
FROM TLX_VENDAS AS V
WHERE V.COMPANY = FN.COMPANY
AND V.SITE = FN.FILIAL
AND V.SERIAL_NO = FN.NUMSERI
AND V.PART_NO = FN.PRODUTO
) AS VE
WHERE FN.LOCAL IN ('17','18')
AND FN.NUMSERI IN ('46774197','41206809','26831647','2925','41552704','42825514','45893206','3528','46774208','42963745','48137580','41601079','41210287','3015192','48860830','44179326','47910283','48031190','48687321','45923658','47962949','49318825','40802021','46618247','47752985','46444583','44104175','46444470','43378548','28297475','47701660','23088358','43381319','23352097','46774677','4822','4703','47881900','47881175','47728987','48318326','47680861','46427019','40401425','44341744','44341745','48521742','27284199','46106793','46103491','47215205','46106792','46106764','46103628','46103712','46103627','47385562','46106710','46103588','46106755','46106744','46106732','44763863','27077336','26994715','40688897','42571588','26698791','46106745','46106708','46106763','46103701','46103625','46106718','26831254','47383688','46106719','46103431','46103429','26690540','46080142','28180776','40720771','28210636','41394485','46559885','46560735', '46566134','27475370','46561815','17206014','41306310','28014050','28193191','45625293','44561283','26753448','26754226','15557861','19489152','47373318','47781575','42769992','44969218','43422275','42772450','45335787','44278083','44272414','44054486','27920321','45106301','48430893','49283529','48978523','48569659','48573357','48125550','25825863','28036290','25319734','25280187','26337586','26192567','26261904','43799292','26598096','25761297','25749913','25825930','25749477','45470401', '25259627','25092311','47223168','47109181','47588116','47587625','47587038','666874','689435','692731','637409','680056','691476','692735','691523','691481','445948','691498','691486','27108831','27585283','27585126','27108132','27901937','27941437','27941449','27585197','27901951','27916993','27108051','27585103','27902120','27901920','27901903','27107040','27108048','27941436','27901904','102610','106134','106106','2285310')