Sum - Subtração do Valor total de dois campos

SQL

Oracle

Firebird

04/11/2021

Preciso somar os campos 'SAIDA' e DEVOLUCAO' separadamente e depois realizar a subtração do valor total dos dois.

select data,
doc,
grupo,
movimentacao,
cdc,
cod,
matmed,
und,
qtde,
cast (custo as numeric (10,2)) custo,
cast (vlrmed as numeric (10,2))vlrmed
from (
select s.data,
s.doc,
'SAIDA' movimentacao,
1 movi,
(select first 1 g.nome from gegrupos g where g.cod = i.gru)grupo,
(select first 1 nome from tbcencus where cod=s.cdc) cdc,
ss.item cod,
i.nome matmed,
i.uni_con und,
sum(qtde) qtde,
cast (custo as numeric (10,2)) custo,
sum(ss.qtde * i.custo) vlrmed
from gecadsai s
left join gelansai ss on s.ano=ss.ano and s.mes=ss.mes and s.doc=ss.doc
left join geitens i on ss.item=i.cod
where ss.consol='T'
and s.data between :data1 and :data2
group by s.data,
s.doc,
i.gru,
s.cdc,
ss.item,
i.nome,
i.uni_con,
s.data,
i.custo


UNION


select d.data,
d.doc,
'DEVOLUÇÃO' movimentacao,
2 movi,
(select first 1 g.nome from gegrupos g where g.cod = ii.gru) grupo,
(select max(nome) from tbcencus where cod=d.cdc) cdc,
l.item cod,
ii.nome matmed,
ii.uni_con und,
sum(qtde) qtde_d,
cast (custo as numeric (10,2)) custo,
sum(l.qtde * ii.custo) vlrmed
from gecaddev d
left join gelandev l on d.id=l.id_gecaddev
left join geitens ii on l.item=ii.cod
where l.consol='T'
and d.data between :data1 and :data2
group by d.data,
d.doc,
ii.gru,
d.cdc,
l.item,
ii.nome,
ii.uni_con,
d.data,
ii.custo
)order by cdc,
matmed,
cod,
qtde,
grupo


HEEELP MEEE
Marcos Batista

Marcos Batista

Curtidas 0

Melhor post

Emerson Nascimento

Emerson Nascimento

04/11/2021

tente assim:
select
	data,
	doc,
	grupo,
	movimentacao,
	cdc,
	cod,
	matmed,
	und,
	saida_qtde,
	cast(saida_custo as numeric (10,2)) sai_custo,
	cast(saida_vlrmed as numeric (10,2)) sai_vlrmed,
	devol_qtde,
	cast(devol_custo as numeric (10,2)) dev_custo,
	cast(devol_vlrmed as numeric (10,2)) dev_vlrmed
from (
	select
		s.data,
		s.doc,
		(select first 1 g.nome from gegrupos g where g.cod = i.gru) grupo,
		(select first 1 nome from tbcencus where cod=s.cdc) cdc,
		ss.item cod,
		i.nome matmed,
		i.uni_con und,
		sum(qtde) saida_qtde,
		cast(custo as numeric (10,2)) saida_custo,
		sum(ss.qtde * i.custo) saida_vlrmed,
		0 devol_qtde,
		0 devol_custo,
		0 devol_vlrmed
	from gecadsai s
	left join gelansai ss on s.ano=ss.ano and s.mes=ss.mes and s.doc=ss.doc
	left join geitens i on ss.item=i.cod
	where ss.consol='T'
		and s.data between :data1 and :data2
	group by
		s.data,
		s.doc,
		i.gru,
		s.cdc,
		ss.item,
		i.nome,
		i.uni_con,
		s.data,
		i.custo

	UNION

	select
		d.data,
		d.doc,
		(select first 1 g.nome from gegrupos g where g.cod = ii.gru) grupo,
		(select max(nome) from tbcencus where cod=d.cdc) cdc,
		l.item cod,
		ii.nome matmed,
		ii.uni_con und,
		0 saida_qtde,
		0 saida_custo,
		0 saida_vlrmed,
		sum(qtde) devol_qtde,
		cast(custo as numeric (10,2)) devol_custo,
		sum(l.qtde * ii.custo) devol_vlrmed
	from gecaddev d
	left join gelandev l on d.id=l.id_gecaddev
	left join geitens ii on l.item=ii.cod
	where l.consol='T'
		and d.data between :data1 and :data2
	group by
		d.data,
		d.doc,
		ii.gru,
		d.cdc,
		l.item,
		ii.nome,
		ii.uni_con,
		d.data,
		ii.custo
) tmp
order
	by cdc,
	matmed,
	cod,
	qtde,
	grupo

você pode agrupar o resultado, para que as informações fiquem numa linha

GOSTEI 1
POSTAR