Boa Tarde Pessoal,
Estou precisando unir 2 query, acredito que se fizer uma subquery possa resolver meu problema so que, não sei como fazer, alguem poderia me ajudar.
Segue abaixo as 2 query;
1ª Query
SELECT
ZR_NUM,ZR_EMISSAO,ZR_CLI,ZR_LOJA,ZR_NOTA,ZS_COD,A2_NREDUZ,A2_LOJA,UD_QTDE,UD_QTDEREC,UD_CODIGO,(UD_QTDE -UD_QTDEREC) AS DIV,B.ZS_FORNECE AS FORNECEDOR,B.ZS_STATUS AS STATUSG,F.UA6_DESC,ZS_DVISTEC,ZS_VISITEC,ZS_ORCVFIL,ZS_FILVGAR,ZS_DSMFGAR,ZS_DEMFGAR,ZS_ORCVGAR,ZS_DSMGFIL,ZS_DEMFIL,ZS_DSAICLI,ZS_ORCCLI,ZS_NFSACLI,B.ZS_LOJA
FROM
SZR010 A
LEFT OUTER JOIN
SZS010 B ON B.D_E_L_E_T_ = ' '
AND B.ZS_FILIAL = A.ZR_FILIAL
AND B.ZS_NUM = A.ZR_NUM
LEFT OUTER JOIN
SUD010 D ON D.D_E_L_E_T_ = ' '
AND D.UD_PRODUTO = B.ZS_COD
AND D.UD_NUMGAR = A.ZR_NUM
AND D.UD_FILORI = A.ZR_FILIAL
AND B.ZS_ITEM = D.UD_ITEMGAR
LEFT OUTER JOIN
SA2010 E ON E.D_E_L_E_T_ = ' '
AND E.A2_COD = B.ZS_FORNECE
AND E.A2_LOJA = B.ZS_LOJA
AND E.A2_FILIAL = B.ZS_FILIAL
LEFT OUTER JOIN
UA6010 F ON F.UA6_COD = D.UD_CODDEF
AND F.UA6_FORNEC = D.UD_FORN
AND F.D_E_L_E_T_ = ' '
LEFT OUTER JOIN
SB1010 G ON G.D_E_L_E_T_ = ' '
AND G.B1_FILIAL = B.ZS_FILIAL
AND G.B1_COD = B.ZS_COD
LEFT OUTER JOIN
SD1010 SD1 ON SD1.D_E_L_E_T_ = ' '
AND SD1.D1_FILIAL IN ('30')
AND SD1.D_E_L_E_T_<>'*'
AND SD1.D1_COD = 'RHO411 '
AND SD1.D1_TES<>' '
AND SD1.D1_FORNECE = '000065'
AND SD1.D1_LOJA = '01'
AND SD1.D1_EMISSAO <='20200218'
AND SD1.D1_SERIE NOT IN ('UNT','UN3','DPL')
AND SD1.D1_TIPO = 'N'
AND SD1.D1_FORMUL <> 'S'
AND SD1.D1_CF NOT IN ('1152','2152','1949','2949','1409','2409')
AND SD1.D1_TES NOT IN ('037','034','035','039','029','041')
WHERE
A.D_E_L_E_T_ = ' '
AND A.ZR_FILIAL IN ('40')
AND A.ZR_NUM BETWEEN ' ' AND 'ZZZZZZ'
AND A.ZR_EMISSAO BETWEEN '20190219' AND '20200219'
AND B.ZS_FORNECE BETWEEN ' ' AND 'ZZZZZZ'
AND B.ZS_NFIND = ' '
AND B.ZS_STATUS NOT IN ('R','I','E')
ORDER BY
A2_NREDUZ, A2_LOJA, B1_GRUPO1, ZR_NUM, ZS_COD
2ª Query, nesta eu preciso apenas do campo D1_DOC dentro da primeira query
SELECT TOP 1
MAX (D1_EMISSAO) AS DTAEMISSAO ,D1_DOC
FROM
SD1010 SD1
LEFT JOIN
SZV010 SZV ON SZV.D_E_L_E_T_ = ' '
AND ZV_NFORI = D1_DOC
AND ZV_COD = D1_COD
WHERE
D1_FILIAL IN ('30')
AND SD1.D_E_L_E_T_<>'*'
AND D1_COD = 'RHO411 '
AND D1_TES<>' '
AND D1_FORNECE = '000065'
AND D1_LOJA = '01'
AND D1_EMISSAO <='20200218'
AND D1_SERIE NOT IN ('UNT','UN3','DPL')
AND D1_TIPO = 'N'
AND D1_FORMUL <> 'S'
AND D1_CF NOT IN ('1152','2152','1949','2949','1409','2409')
AND D1_TES NOT IN ('037','034','035','039','029','041')
GROUP BY
D1_COD, D1_UM, D1_VUNIT, D1_EMISSAO, D1_DOC, D1_SERIE, D1_IPI, D1_PICM, D1_EMP, D1_ITEM, D1_QUANT, D1_QTDEDEV
HAVING
D1_QUANT-SUM(CASE WHEN ZV_QTD IS NULL THEN 0 ELSE ZV_QTD END) > 0 AND (D1_QUANT - D1_QTDEDEV) > 0
ORDER BY
D1_EMISSAO DESC
