See table by period

Asked

Viewed 66 times

2

I have two tables, "Contact" and "Pedvenda", how can I make a query that manages during a period, for example, from 01/01/2019 to 30/01/2019 the quantity of sales between 10:00h and 16:00h each day 01/01, 02/01, 03/01 and so on until 30/01? The "Contact" table I mentioned because I need to get the name of the customer that is on it.

That way I can take the whole period and also I can run the query considering only the day, but I wanted to know if I can solve everything in just a query from 01/01/2019 to 30/01/2019 picking up every day.

SELECT P.PvCod, C.ContNome, P.EmpCod, P.PvDatHor, P.PvStatus, P.PvValTot
FROM PedVend P, Contato C 
WHERE P.ContCod = C.ContCod
and P.EmpCod = 55 
and P.PvDatHor BETWEEN '26/12/2018 10:00:00' and '26/12/2018 16:00:01'
sql sql-server sql-server-2014

  • Put the desired period in the BETWEEN, add a GROUP BY clause per day and use the COUNT aggregation function.

  • So, to get this right, I would have to add the GROUP BY clause at the end of WHERE and in SELECT use the COUNT function ? If this is an error saying that the other SELECT fields are invalid in the selection list because they do not use aggregation function.

  • Your doubt would be in the Where clause?

  • This, along with the GROUP BY function

  • WHERE (P.Pvdathor BETWEEN @DATAINICIO AND GETDATE()) AND (DATEPART(HOUR, P.Pvdathor) BETWEEN 10 AND 16) Try this on the date of your Where. And I think there is no need to use group by, depending on your need

2 answers

1

I did a test on the structure I have here, and so it worked, if you want exactly the amount and not the amount of record, you should replace the columns with a Count() and include a group by at the end of the query. 

DECLARE @DATAINICIO DATETIME;
DECLARE @DATAFINAL DATETIME;
    SET @DATAINICIO = --DATAINICIO    
    SET @DATAFINAL = --DATAFINAL
SELECT P.PvCod, C.ContNome, P.EmpCod, P.PvDatHor, P.PvStatus, P.PvValTot FROM PedVend P, Contato C 
        WHERE P.ContCod = C.ContCod and P.EmpCod = 55 and (P.PvDatHor BETWEEN @DATAINICIO AND @DATAFINAL) AND (DATEPART(HOUR, P.PvDatHor) BETWEEN 10 AND 16)

  • In this case, don’t take the daily amount right? 01/01, 02/01, 03/01 and so on until 30/01. Sorry, I’m just trying to better understand the code.

  • To bring the quantity you must group the data, add a group by at the end of the query and a Count(P.Pvcod) for the columns.

  • Thanks for the help!

  • I hope I’ve helped! :)

0

The query below returns the amount of records in PedVend (using the filter EmpCod = 5 and hours between 10:00 and 16:00), grouped by date.

SELECT P.PvDatHor, count(*)
FROM PedVend P
WHERE P.EmpCod = 55 
and cast(P.PvDatHor as time) BETWEEN '10:00:00' and '16:00:01'
GROUP BY CAST(P.PvDatHor AS DATE)

  • Thanks for the help!

  • This helped to solve the problem?

  • Yes, I made only a few changes but it helped a lot to be aware of how to implement here.

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.