//comando para totalizar as vendas do dia. no exemplo abaixo foi totalizado as vendas do dia 17/05/2012 da loja 1
select sum( coalesce(Total,0) + coalesce(Servico,0) + coalesce(Frete,0) - coalesce(Desconto,0)) as Total from oe where data = '05/17/2012'
and oe.LOJA = 1 and OE.Tipo IN ('V', 'L', 'D', 'N') AND (OE.Canc <> 'S' OR OE.Canc IS NULL)
//Comando que exibe o valor total vendido de itens. No comando abaixo, esta sendo exibido o total vendido de itens com cst '060' na data de 17/05/2012 da loja 1
select sum(coalesce(ioe.quant,0)*coalesce(ioe.valor,0) ) as Total from ioe where data = '05/17/2012' and
exists(select 1 from oe where oe.data=ioe.data and oe.id_oe=ioe.id_oe and oe.LOJA=1 and oe.Tipo IN ('V', 'L', 'D', 'N') AND (OE.Canc <> 'S' OR OE.Canc IS NULL))
and (select cst from prod where prod.cod=ioe.cod) = '060'
//Comando abaixo une dois comandos (UNION)
select sum(coalesce(ioe.quant,0)*coalesce(ioe.valor,0) ) as Total from ioe where data = '05/17/2012' and
exists(select 1 from oe where oe.data=ioe.data and oe.id_oe=ioe.id_oe and oe.LOJA=1 and oe.Tipo IN ('V', 'L', 'D', 'N') AND (OE.Canc <> 'S' OR OE.Canc IS NULL))
and (select cst from prod where prod.cod=ioe.cod) = '060'
union
select sum(coalesce(ioe.quant,0)*coalesce(ioe.valor,0) ) as Total from ioe where data = '05/17/2012' and
exists(select 1 from oe where oe.data=ioe.data and oe.id_oe=ioe.id_oe and oe.LOJA=1 and oe.Tipo IN ('V', 'L', 'D', 'N') AND (OE.Canc <> 'S' OR OE.Canc IS NULL))
and (select cst from prod where prod.cod=ioe.cod) = '041'
|