Get Total value with the exception of date

Navigation
1

I am trying to develop a query that returns me a total value of requests from a given client, with an informed date range. For example, between 05/11/2015 and 05/12/2015.

The problem is that I need to make the exception for a few days between the reported dates. For example, let this query take the value between the dates given above, except on 11/14/2015 and 11/21/2015.

These days could not have the value of your orders added in the total amount.

What I was able to do was this: 

select sum(p.totalValor), c.nomeCliente 
from Pedido p join Cliente c 
on c.idCliente =  p.idCliente
where p.dataPedido = /*filtro das datas*/
and idCliente = 2

I could not develop the dates part. Is there any way you can do this?

The tables are below

Customer table

CustomerID CustomerName

Order table

id Order CustomerID data requested totalValue

    
asked by anonymous 09.12.2015 / 14:34

1 answer

1

To search for a date range use the BETWEEN operator and to exclude some dates use the NOT IN .

Since you are using the SUM function you need to use the GROUP BY in your query. 

SELECT 
    SUM(P.TOTALVALOR),
    C.NOMECLIENTE
FROM 
    PEDIDO P 
JOIN 
    CLIENTE C ON C.IDCLIENTE =  P.IDCLIENTE
WHERE 
    DATAPEDIDO BETWEEN '05/11/2015' AND '05/12/2015'
AND
    DATAPEDIDO NOT IN ('14/11/2015', '21/11/2015')
AND 
    IDCLIENTE = 2
GROUP BY C.NOMECLIENTE
    
09.12.2015 / 14:38
Client Server error in sending messages Post null in the MVC edit