Exclude a range of dates for the value of a particular field

0

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
    
asked by Emilio Galarraga 09.04.2018 в 19:12
source

1 answer

0

I found the solution using the NOT function in mysql

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 NOT(tienda='tienda01' and fecha_venta between '2017-07-05' and '2017-07-15')
group by tienda order by monto desc

Now if it works for me

    
answered by 09.04.2018 / 20:12
source