I made a query
with two selects
using union
and it has two columns: MOBI
and DESMOBI
. I need the result to show these two columns. when I execute it only shows the MOBI 'column. Below is the query. I look forward to your help.
SELECT
RE.cd_flremequ AS RemessaID
,DE.cd_flremequ AS DevID
,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
,F.numero AS ficha
,R.sequencia AS num_remessa
,D.sequencia AS num_devolucao
,SUM(RE.qt_remessa) AS qt_remessa
,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
,RE.vl_uni_locacao
,SUM(RE.qt_remessa) * RE.vl_uni_locacao AS MOBI
,G.CD_GRUPO
,F.cd_pessoa_fun AS cod_vendedor
,VENDEDOR.nm_pessoa AS vendedor
,E.nm_equipto
,P.nr_patrimonio
,F.cd_pessoa AS cod_cli
,C.nm_pessoa
FROM
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo
WHERE
(R.dt_saida IS NOT NULL AND R.dt_saida_sis IS NOT NULL)
AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/
AND ((R.dt_saida between '2017-01-01' AND '2017-07-31'))
/*AND fl_equipto_pesado = 'S' */
AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)
GROUP BY
RE.cd_flremequ
,DE.cd_flremequ
,P.nr_patrimonio
,R.dt_saida
,F.numero
,R.sequencia
,RE.qt_remessa
,RE.vl_uni_locacao
,DE.qt_devolucao
,RE.vl_uni_locacao
,RE.cd_pessoa
,E.nm_equipto
,C.nm_pessoa
,F.cd_pessoa_fun
,F.cd_pessoa
,vendedor.nm_pessoa
,D.sequencia
,E.CD_GRUPO
,G.CD_GRUPO
,e.fl_pat_equipto
,D.dt_cobranca
,RE.qt_devolucao
UNION
SELECT
RE.cd_flremequ AS RemessaID
,DE.cd_flremequ AS DevID
,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
,F.numero AS ficha
,R.sequencia AS num_remessa
,D.sequencia AS num_devolucao
,SUM(RE.qt_remessa) AS qt_remessa
,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
,RE.vl_uni_locacao
,SUM(DE.qt_devolucao * RE.vl_uni_locacao) AS 'DESMOBI'
,G.CD_GRUPO
,F.cd_pessoa_fun AS cod_vendedor
,vendedor.nm_pessoa AS vendedor
,E.nm_equipto
,P.nr_patrimonio
,F.cd_pessoa AS cod_cli
,C.nm_pessoa
FROM
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo
WHERE (D.dt_cobranca between '2017-01-01' AND '2017-07-31')
AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)
AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/
GROUP BY
RE.cd_flremequ
,DE.cd_flremequ
,P.nr_patrimonio
,R.dt_saida
,F.numero
,R.sequencia
,RE.qt_remessa
,RE.vl_uni_locacao
,DE.qt_devolucao
,RE.vl_uni_locacao
,RE.cd_pessoa
,E.nm_equipto
,C.nm_pessoa
,F.cd_pessoa_fun
,F.cd_pessoa
,vendedor.nm_pessoa
,D.sequencia
,E.CD_GRUPO
,G.CD_GRUPO
,e.fl_pat_equipto
,D.dt_cobranca
,RE.qt_devolucao
ORDER BY F.numero DESC