I have these three querys , and I want to merge them using outer apply .
How can I do this?
SELECT
SR1.COMPANY,
SR1.FILIAL,
SR1.DOC,
SR1.SERIE,
SR1.LOCAL,
SR1.TIPONF,
SR1.CLIFOR,
SR1.ESTORNO,
NF.CCUSTO,
SR1.PRODUTO,
P.DESCR,
ISNULL (A2.A2_COD,A1_COD) [COD FORNECEDOR],
ISNULL (A2.A2_NOME,A1_NOME) [NOME FORNECEDOR],
SR1.ORIGEM,
SR1.NUMSERI,
CAST(CONCAT(SR1.DATA, ' ', SR1.HRINI) AS DATETIME) [DATA DE VENDA],
CASE SR1.FLAG_TM WHEN 'E' THEN 'ENTRADA' WHEN 'S' THEN 'SAIDA' END AS FLAG
FROM TLX_INVENTORY_TRANS_HIST SR1 WITH (NOLOCK)
LEFT JOIN TLX_PRODUTOS_INFO P WITH (NOLOCK)
ON P.COD = SR1.PRODUTO
LEFT JOIN TLX_NFI_ENTRADASAIDA NF WITH (NOLOCK)
ON NF.COMPANY = SR1.COMPANY AND NF.FILIAL = SR1.FILIAL AND NF.DOC = SR1.DOC AND NF.SERIE = SR1.SERIE AND NF.NUMSEQ = SR1.NUMSEQ
AND NF.COD = SR1.PRODUTO AND NF.FLAG = SR1.FLAG_TM
LEFT JOIN SA1010 A1 WITH (NOLOCK)
ON A1_COD = SR1.CLIFOR AND A1_LOJA = SR1.LOJA AND A1.D_E_L_E_T_ = ''
AND ((SR1.FLAG_TM = 'S' AND SR1.TIPO NOT IN ('B', 'D')) OR (SR1.FLAG_TM = 'E' AND SR1.TIPO IN ('B', 'D')))
LEFT JOIN SA2010 A2 WITH (NOLOCK)
ON A2_COD = SR1.CLIFOR AND A2_LOJA = SR1.LOJA AND A2.D_E_L_E_T_ = ''
AND ((SR1.FLAG_TM = 'S' AND SR1.TIPO IN ('B', 'D')) OR (SR1.FLAG_TM = 'E' AND SR1.TIPO NOT IN ('B', 'D')))
WHERE CONCAT(SR1.NUMSERI,SR1.PRODUTO,SR1.DATA)
IN (
SELECT TOP 1
CONCAT(SR2.NUMSERI,SR2.PRODUTO,MAX(SR2.DATA))
FROM
TLX_INVENTORY_TRANS_HIST SR2 WITH (NOLOCK)
WHERE
SR2.PRODUTO = SR1.PRODUTO AND SR2.NUMSERI = SR1.NUMSERI AND SR2.FLAG_TM ='S'
GROUP BY SR2.NUMSERI, SR2.PRODUTO
) AND
SR1.COMPANY = 'TX' AND SR1.FILIAL IN ('01','29') AND SR1.NUMSERI /*= '46774197'*/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') AND FLAG_TM = 'E'
ORDER BY NUMSERI
SELECT
FNI.COMPANY,
FNI.FILIAL,
FNI.DOC,
FNI.SERIE,
FNI.LOCAL,
FNI.TIPONF,
FNI.CLIFOR,
FNI.ESTORNO,
NF.CCUSTO,
FNI.PRODUTO,
P.DESCR,
ISNULL (A2.A2_COD,A1_COD) [COD FORNECEDOR],
ISNULL (A2.A2_NOME,A1_NOME) [NOME FORNECEDOR],
FNI.ORIGEM,
FNI.NUMSERI,
CAST(CONCAT(FNI.DATA, ' ', FNI.HRINI) AS DATETIME) [DATA PRIMEIRA SAIDA],
CASE FNI.FLAG_TM WHEN 'E' THEN 'ENTRADA' WHEN 'S' THEN 'SAIDA' END AS FLAG
FROM TLX_INVENTORY_TRANS_HIST FNI WITH (NOLOCK)
LEFT JOIN TLX_PRODUTOS_INFO P WITH (NOLOCK)
ON P.COD = FNI.PRODUTO
LEFT JOIN TLX_NFI_ENTRADASAIDA NF WITH (NOLOCK)
ON NF.COMPANY = FNI.COMPANY AND NF.FILIAL = FNI.FILIAL AND NF.DOC = FNI.DOC AND NF.SERIE = FNI.SERIE AND NF.NUMSEQ = FNI.NUMSEQ
AND NF.COD = FNI.PRODUTO AND NF.FLAG = FNI.FLAG_TM
LEFT JOIN SA1010 A1 WITH (NOLOCK)
ON A1_COD = FNI.CLIFOR AND A1_LOJA = FNI.LOJA AND A1.D_E_L_E_T_ = ''
AND ((FNI.FLAG_TM = 'S' AND FNI.TIPO NOT IN ('B', 'D')) OR (FNI.FLAG_TM = 'E' AND FNI.TIPO IN ('B', 'D')))
LEFT JOIN SA2010 A2 WITH (NOLOCK)
ON A2_COD = FNI.CLIFOR AND A2_LOJA = FNI.LOJA AND A2.D_E_L_E_T_ = ''
AND ((FNI.FLAG_TM = 'S' AND FNI.TIPO IN ('B', 'D')) OR (FNI.FLAG_TM = 'E' AND FNI.TIPO NOT IN ('B', 'D')))
WHERE CONCAT(FNI.NUMSERI,FNI.PRODUTO,FNI.DATA)
IN (
SELECT TOP 1
CONCAT(SR2.NUMSERI,SR2.PRODUTO,MIN(SR2.DATA))
FROM
TLX_INVENTORY_TRANS_HIST SR2 WITH (NOLOCK)
WHERE
SR2.PRODUTO = FNI.PRODUTO AND SR2.NUMSERI = FNI.NUMSERI AND SR2.FLAG_TM ='S'
GROUP BY SR2.NUMSERI, SR2.PRODUTO
) AND
FNI.COMPANY = 'TX' AND FNI.FILIAL IN ('01','29') AND FNI.NUMSERI /*= '46774197'*/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') AND FLAG_TM = 'S'
SELECT
FN.COMPANY,
FN.FILIAL,
FN.DOC,
FN.SERIE,
FN.LOCAL,
FN.TIPONF,
FN.CLIFOR,
FN.ESTORNO,
NF.CCUSTO,
NF.CCUSTO,
FN.PRODUTO,
P.DESCR,
ISNULL (A2.A2_COD,A1_COD) [COD FORNECEDOR],
ISNULL (A2.A2_NOME,A1_NOME) [NOME FORNECEDOR],
FN.ORIGEM,
FN.NUMSERI,
CAST(CONCAT(FN.DATA, ' ', FN.HRINI) AS DATETIME) [DATA PRIMEIRA ENTRADA],
CASE FN.FLAG_TM WHEN 'E' THEN 'ENTRADA' WHEN 'S' THEN 'SAIDA' END AS FLAG
FROM TLX_INVENTORY_TRANS_HIST FN WITH (NOLOCK)
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')))
WHERE CONCAT(FN.NUMSERI,FN.PRODUTO,FN.DATA)
IN (
SELECT TOP 1
CONCAT(SR2.NUMSERI,SR2.PRODUTO ,MIN(SR2.DATA))
FROM
TLX_INVENTORY_TRANS_HIST SR2 WITH (NOLOCK)
WHERE
FN.PRODUTO = FN.PRODUTO AND SR2.NUMSERI = FN.NUMSERI
GROUP BY SR2.NUMSERI, SR2.PRODUTO
) AND
FN.COMPANY = 'TX' AND FN.FILIAL IN ('01','29') AND FN.NUMSERI /*= '46774197'*/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') AND FLAG_TM = 'E'
ORDER BY NUMSERI