Sales Ranking Report

Asked

Viewed 1,358 times

3

I use this SQL command to make a sales ranking

SQL:

SELECT VENDEDOR,SUM(TOTAL) AS GERAL FROM VENDAS
GROUP BY VENDEDOR
ORDER BY SUM(TOTAL) DESC;

Upshot:

VENDEDOR | VALOR
FULANO1  | 1000
FULANO2  | 800
FULANO3  | 700
FULANO4  | 100

I would like to make this same result between dates, only the result in what I did, it makes day by day. More not want day by day but the total.

The SQL I made was like this:

SQL:

SELECT DATAVENDA,VENDEDOR,SUM(TOTAL) AS GERAL FROM VENDAS
GROUP BY DATAVENDA,VENDEDOR
ORDER BY DATAVENDA,SUM(TOTAL) DESC;

Upshot:

 DATAVENDA | VENDEDOR | TOTAL
01/04/2015 |FULANO1   | 700
01/04/2015 |FULANO2   | 600
02/04/2015 |FULANO1   | 300
02/04/2015 |FULANO2   | 200

Use only one table with the name of SALES and the fields are:

DATAVENDA(TIPO DATA),VENDEDOR(TIPO STRING),VALOR(TIPO MOEDA).

There’s a way or a way to make it work?

sql-server

  • Do you want the total result between two dates? If so, you need to change your SQL slightly. Example: SELECT VENDEDOR, SUM(TOTAL) AS GERAL FROM VENDAS WHERE DATAVENDA BETWEEN '2014-12-04' AND '2014-12-08' GROUP BY VENDEDOR ORDER BY SUM(TOTAL) DESC;

  • Friend, sorry I’m new here, the more I use is the sqlserver.

  • Rafael has already given an error, because select be missing date. select datavenda,vendedor, sum(total) as geral from vendas Where datavenda between '2014-12-04' and '2014-12-08' group by vendedor order by sum(total) desc

  • 1

    Is there an example of what end result you would like exactly? Something like? DataVenda: Dia inicial - Dia Final | Total: 500 | Vendedor: XYZ ?

  • It cannot be a select where there is another select inside. Type, do the search between dates and then make the total sum of each seller???

  • Without knowing what the end result you want, we have no way to help. As you had asked for a result showing only the total and not date by date, I had put it above... but you said you need the date, so... what is the final result expected by you?

  • The result is the total sum of sales of each sales each seller by period. Start date and end date.

  • I have no idea if it will work or not, but do a test with this SQL: SELECT VENDEDOR, CONCAT(MAX(DATAVENDA),' ', MIN(DATAVENDA)) AS DATAS, SUM(TOTAL) AS GERAL FROM VENDAS WHERE DATAVENDA BETWEEN '2014-12-04' AND '2014-12-08' GROUP BY VENDEDOR ORDER BY SUM(TOTAL) DESC;

  • I’ll test it, as soon as it works out here I’ll let you know later today. See you.

  • Rafael his first solution worked perfectly. The second failed to execute, but I am already grateful. Obg and until the next colleague.

  • It wouldn’t just take the date of the grouping !?

  • How would I look without the motta cluster??

Show 7 more comments

1 answer

1

I think it might help in future doubts with a similar situation:

Assuming the following data source

datavenda   vendedor    valor
2015-04-01  FULANO1 700,00
2015-04-02  FULANO1 300,00
2015-04-01  FULANO2 600,00
2015-04-02  FULANO2 200,00
2015-04-01  FULANO3 400,00
2015-04-02  FULANO3 300,00
2015-04-01  FULANO4 30,00
2015-04-02  FULANO4 70,00
2015-04-01  FULANO1 1500,00
2015-04-01  FULANO1 350,00
2015-04-02  FULANO1 750,00
2015-04-02  FULANO1 300,00
2015-04-01  FULANO2 1800,00
2015-04-01  FULANO3 600,00
2015-04-01  FULANO4 800,00
2015-04-03  FULANO1 1500,00
2015-04-03  FULANO2 3000,00
2015-04-04  FULANO4 6000,00

The query below brings the total of each seller between an initial date and a final date.

declare @dt_inicio date
declare @dt_fim date
set @dt_inicio = '2015-04-01'
set @dt_fim = '2015-04-04'

  SELECT distinct @dt_inicio as [inicio],
         @dt_fim as [fim],
         A.vendedor, soma.v as [TOTAL] 

  from VENDAS AS A

  cross apply (

                select sum(valor) v from VENDAS 
                where datavenda between @dt_inicio and @dt_fim 
                and vendedor = A.vendedor 

               ) soma

  where datavenda between @dt_inicio and @dt_fim

  order by soma.v DESC

Upshot:

2015-04-01  2015-04-04  FULANO4 6900,00
2015-04-01  2015-04-04  FULANO2 5600,00
2015-04-01  2015-04-04  FULANO1 5400,00
2015-04-01  2015-04-04  FULANO3 1300,00

To increment, we can add the corresponding % to each seller within the searched period.

SELECT distinct @dt_inicio as [inicio],
         @dt_fim as [fim],
         A.vendedor, soma.v as [TOTAL],
         format((soma.v / soma2.v), 'p') as [% sobre TOTAL NO PERÍODO]

  from VENDAS AS A

  cross apply (

                select sum(valor) v from VENDAS 
                where datavenda between @dt_inicio and @dt_fim 
                and vendedor = A.vendedor 

               ) soma

   cross apply (

                select sum(valor) v from VENDAS 
                where datavenda between @dt_inicio and @dt_fim                 

               ) soma2

  where datavenda between @dt_inicio and @dt_fim

  order by soma.v DESC

Upshot:

2015-04-01  2015-04-04  FULANO4 6900,00 35,93%
2015-04-01  2015-04-04  FULANO2 5600,00 29,16%
2015-04-01  2015-04-04  FULANO1 5400,00 28,12%
2015-04-01  2015-04-04  FULANO3 1300,00 6,77%

I have a habit of always saving dates in American format. To format pro Brazil just use: 

...
 format(@dt_inicio, 'dd/MM/yyyy')as [inicio],
 format(@dt_fim, 'dd/MM/yyyy')as [fim], ...

If you prefer, you don’t have to leave the dates @dt_inicio as [inicio], @dt_fim as [fim] no select. You can withdraw to return on the lines only the name and total of each seller.

Browser other questions tagged sql-server

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