Voucher count showing all months

Question

Voucher count showing all months

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a voucher count that is already done and I get bounced by date, now the problem that I want to show all the months of the year, could help me in this function that my head is flying. that friends advise me

select sucursal.nombre,venta.ejercicio,venta.mov, count(*) [Enero] from 
venta (nolock)
left outer join sucursal (nolock) on venta.sucursal = sucursal.sucursal
left outer join movtipo (nolock) on venta.mov = movtipo.mov and 
movtipo.modulo = 'vtas' and 
movtipo.clave in 
('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
where venta.ejercicio = '2018'and
  venta.periodo = '1' and
  venta.mov not like 'E-%' and
  movtipo.clave in 
('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
group by sucursal.nombre,venta.ejercicio,venta.mov

sql sql-server

asked by Many Porras Avila 07.12.2018 в 15:49

source

2 answers

Database: a table by "section" or a single table with all the capos truncate decimal

score 0 · Answer 1

I think the best way to do this grouping by month is as follows:

select sucursal.nombre,venta.ejercicio,venta.mov, venta.periodo AS mes,  
count(venta.periodo) total_mes from 
venta (nolock)
left outer join sucursal (nolock) on venta.sucursal = sucursal.sucursal
left outer join movtipo (nolock) on venta.mov = movtipo.mov and 
movtipo.modulo = 'vtas' and 
movtipo.clave in 
('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
where venta.ejercicio = '2018'and
   venta.mov not like 'E-%' and
   movtipo.clave in 
     ('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
group by sucursal.nombre,venta.ejercicio,venta.mov,venta.periodo

In the end, this will not generate a column for each month, but it will add the month and the number of associated sales for each record. With that you can iterate to make listings in HTML or in the technology that you occupy as frontend.

To do it as you want, although it is possible, it is not very optimal since you would have to make a selection for each month (nested Queries if you want to investigate).

Try that and tell me how it works for you, since not knowing the complete structure of your table could have some error

I hope to contribute

score 0 · Answer 2

with comprobante (Sucursal, Tipo_de_comprobante, Año, Mes, Total) as
(select sucursal.nombre, venta.mov, venta.ejercicio, venta.periodo, count(venta.mov) 
Total from venta (nolock)
left outer join sucursal (nolock) on venta.sucursal = sucursal.sucursal
left outer join movtipo (nolock) on venta.mov = movtipo.mov and movtipo.modulo = 
'vtas' and 
movtipo.clave in ('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
where venta.ejercicio = '2018'and
  venta.mov not like 'E-%' and
  movtipo.clave in 
 ('vtas.fb','vtas.f','vtas.fc','vtas.fg','vtas.d','vtas.dc','vtas.b')
group by sucursal.nombre, venta.mov, venta.ejercicio, venta.periodo )
select * from comprobante 
pivot (sum(Total) for Mes in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
[11], [12])) PVT