calendario | JGInformática

Olá,
este artigo sendo geral aplica-se , principalmente, à área de vendas a retalho onde o horário de abertura e de fecho das lojas é fundamental para, nomeadamente, o cálculo de objectivos de vendas.

Todos os meses será necessário o cálculo de objectivos de vendas para cada loja, sendo necessário planear o calendário de abertura/fecho para cada loja, principalmente nesta altura com tantas alterações repentinas por causa do COVID.

As ferramentas de suporte ao negócio apenas podem ajudar efectivamente o negócio após terem sido definidos os calendários mensais de cada loja. Esta acção deve ser desenvolvida pelos gerentes ou supervisores de lojas uma vez serem eles os detentores do conhecimento local.

A tarefa pode ser facilitada se lhes for fornecido um calendário pré-preenchido, ficando a seu encargo a validação e alguma alteração esporádica.

Este processo pode ser baseado nos horários do mês anterior, se determinada loja tem um horário “x” este mês será provável que para o próximo mês se mantenha.
Mas aqui a proposta vai ainda mais longe, vamos basear o cálculo dos horários futuros na maior frequência de horário do mês anterior, isto é, na moda, para cada dia da semana.

Para se perceber melhor examinemos o seguinte cenário:
Tomemos por base as sextas-feiras deste mês, para a loja nº1. Existem 4 sextas, com horário de abertura igual (open) e horário de fecho diferente (close).

Percebe-se que a maior frequência (Moda estatística) para a hora de fecho é às 22 horas. Será este valor que deve ficar registado no horário de fecho das sextas-feiras do próximo mês a calcular (Junho). O cálculo para todos os outros dias de semana, para todas as lojas, é semelhante.

Iniciemos por analisar a tabela do calendário para o mês de Maio e, para facilitar, apenas para a loja nº 11:

O weekday 1 decidiu-se ser a segunda-feira.

Vamos começar por calcular a Moda da hora de abertura do mês corrente e, para simplificar, apenas para a loja 11:

A coluna contador faz a contagem do número de vezes que cada hora de abertura está presente. A coluna open_serial_rank, baseada na partição por store/weekday, faz também uma contagem colocando o valor mais baixo para a contagem mais alta, isto é, o número mais frequente (maior valor em contador) é o que tem valor 1 (menor valor em open_serial_rank).

Repare-se por exemplo no weekday=4, tem 3 horários de entrada às 10 horas e 1 horário às 24 (na prática está fechado aqui!) – a Moda será 10. Já no weekday=7, o valor mais frequente para o horário de abertura é 24, isto é, a loja esteve maioritariamente fechada – será este estado a reflectir em Junho.

Apliquemos, agora, o mesmo processo para o cálculo da hora de fecho:

Repare-se por exemplo no weekday = 4 com 3 contagens de hora de fecho às 22 e uma contagem às 24 (fechado) – a Moda será 22, conforme especificado por close_serial_rank = 1.

Os valores da Moda serão os que se devem colocar posteriormente no calendário de Junho.

Agora basta juntar os dois códigos e obteremos a hora de abertura e a hora de fecho com mais frequência, isto é, onde open_serial_rank = 1 e close_serial_rank = 1:

/*
MODA ESTATÍSTICA SQL,
2021 JG
*/
select * from 
(
select  
	store,
	weekday, 
	open_serial,
	count(*) contador, 
	row_number() over (partition by store,weekday order by count(*) desc) open_serial_rank
from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
where 
		date between '2021-05-01' and '2021-05-31'
		and store=11		
group by store, weekday, open_serial 		
) KK
INNER JOIN
(
select  
	store AS STORE_, 
	weekday AS WEEKDAY_, 
	close_serial, 
	count(*) CONTADOR_, 
	row_number() over (partition by store,weekday order by count(*) desc) close_serial_rank
from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
where 
		date between '2021-05-01' and '2021-05-31'
		and store=11		
group by store, weekday, close_serial
) JJ 
on KK.store = JJ.STORE_ and KK.weekday = JJ.WEEKDAY_
where open_serial_rank=1 and close_serial_rank=1

Resultado:

Para cada um dos dias de semana ([1..7]) teremos agora a Moda de abertura (open_serial) e a Moda de fecho (close_serial). No Domingo, correspondente ao weekday 7, a loja estará fechada.

Para finalizar, basta cruzar este código com a tabela calendário para o mês futuro, Junho, assumindo as colunas de hora de abertura e fecho como sendo open_moda e close_moda, respectivamente:

/*
MODA ESTATÍSTICA SQL,
2021 JG
*/

select  HH.store,
	HH.date,
	HH.weekday,
	DD.open_serial AS open_moda, 
	DD.close_serial AS close_moda 
from [dbo].[CALENDARIO_LOJAS] (NOLOCK) HH
LEFT JOIN
(
	select * from 
	(
	select  
		store,
		weekday, 
		open_serial,
		count(*) contador, 
		row_number() over (partition by store,weekday order by count(*) desc) open_serial_rank
	from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
	where 
			date between '2021-05-01' and '2021-05-31'
			and store=11		
	group by store, weekday, open_serial 		
	) KK
	INNER JOIN
	(
	select  
		store AS STORE_, 
		weekday AS WEEKDAY_, 
		close_serial, 
		count(*) CONTADOR_, 
		row_number() over (partition by store,weekday order by count(*) desc) close_serial_rank
	from [dbo].[CALENDARIO_LOJAS] (NOLOCK)
	where 
			date between '2021-05-01' and '2021-05-31'
			and store=11		
	group by store, weekday, close_serial
	) JJ 
	on KK.store = JJ.STORE_ and KK.weekday = JJ.WEEKDAY_
	where open_serial_rank=1 and close_serial_rank=1
) DD
ON HH.STORE = DD.STORE AND HH.WEEKDAY = DD.WEEKDAY
WHERE 
HH.date between '2021-06-01' and '2021-06-30'
and HH.store=11

O resultado final com os “novos” horários para Junho, aqui com amostragem até dia 18, é: