Sales ranking report

Navigation
3

I use this SQL command to do a sales ranking

SQL: 

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

Result: 

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

I would like to do this same result between dates, but the result in what I did, it does day by day. More I do 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;

Result: 

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

I use only one table with the SALES name and the fields are: 

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

Is there a way or way to make it work?

    
asked by anonymous 08.04.2015 / 20:09

1 answer

1

I think it might help with future questions 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 a start date and an end 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
  

Result: 

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 increase, we can add the corresponding percentage to each salesperson within the search 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
  

Result: 

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 keeping dates in the American format. To format for Brazil just use: 

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

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

    
05.02.2017 / 00:29
How to get the next month and year in classic ASP? How do I connect to a specific Wi-Fi network from my application?