I have a table called facturacion_tiendas with the following fields tienda, fecha_venta ,umped_until The stores are tienda01, tienda02, tienda03
I need to extract the sales for a month but I do not take into account a lapse of time when dealing with a particular store
When I want to show the amount sold by all stores I use the query
select distinct(tienda) as Tienda, sum(monto_vendido) as monto
from facturacion_tiendas
where fecha_venta between '2017-07-01' and '2017-07-31'
group by tienda order by monto desc
If I only took one store and I want to exclude what I sold in a certain period of time, example between 05-07-2017 and 07-15-2017
I use this query
select distinct(tienda) as Tienda, sum(monto_vendido) as monto
from facturacion_tiendas
where fecha_venta between '2017-07-01' and '2017-07-31'
and tienda='tienda01'
and fecha_venta not between '2017-07-05' and '2017-07-15'
group by tienda order by monto desc
The problem that arises is when I want to obtain the sales of all the stores in a period of time with the exception of what the store01 sold in a sub period that period
select distinct(tienda) as Tienda, sum(monto_vendido) as monto
from facturacion_tiendas
where fecha_venta between '2017-07-01' and '2017-07-31'
and (fecha_venta not between '2017-07-05' and '2017-07-15' and tienda='tienda01')
group by tienda order by monto desc
This query does not work for me
I tried to do it with case when but it did not work for me either
select distinct(tienda) as Tienda, sum(monto_vendido) as monto
from facturacion_tiendas
where fecha_venta between '2017-07-01' and '2017-07-31'
and when case tienda=tienda01 then (fecha_venta not between '2017-07-05' and '2017-07-15')
group by tienda order by monto desc