0
I have these three querys, and I want to put them together using the outer apply.
How can I do that?
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
What are the 3 querys? I could not identify, anything click on edit and separate them.
– user28595
I separated them with this ---------------------------------- ?
– Leonardo Palmieri
If I understood all three correctly selects consult the same table, what differentiates is the condition (Where) of each. Wouldn’t you just be the case? Something like
"where da primeira" or "where da segunda" or "where da terceira"??– rLinhares
Yes all three have the same table in my select, But it turns out that for the report to stay the way they asked, I need to do it this way. For then I will have to put the 3 dates, of the three querys, in different columns
– Leonardo Palmieri
You would know how to do this junction of the three querys, with Outer apply?
– Leonardo Palmieri
@Leonardopalmieri already tried using a UNION ALL with the 3 querys? would not meet you?
– Ricardo Souza
@Ricardosouza Ja yes, but unfortunately did not answer me
– Leonardo Palmieri