Média em minutos

Question

Tenho a estruturra abaixo no formato datetime onde preciso saber a média em minutos do atendimento.

joao 18/02/2012 10:50:09 finalizou atendimento

joao 18/02/2012 10:34:09 iniciou atendimento

Utilizando o script consigo a média 2012-06-18 22:42:09.000

SELECT CAST(AVG(CAST(data AS FLOAT)) AS DATETIME)
FROM teste

Answer 1

Diego,

A hora de inicio e fim estão no mesmo registro? Ou são registros diferentes?

Um atendimento pode durar mais do que um dia?

No exemplo que você usou, e sem saber das respostas acima, veja o código:

DECLARE @Inicio DATETIME
DECLARE @Fim DATETIME 


SET @Inicio = '2012-02-18 10:34:09'
SET @Fim = '2012-02-18 10:50:09'


SELECT DATEDIFF(MINUTE, @INICIO, @FIM)

Coloque mais detalhes da estrutura da sua tabela e das possibilidades dos dados para que possamos ajudar com o script completo.

---

Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

Answer 2

Diego,

A hora de inicio e fim estão no mesmo registro? Ou são registros diferentes?

Um atendimento pode durar mais do que um dia?

No exemplo que você usou, e sem saber das respostas acima, veja o código:

DECLARE @Inicio DATETIME
DECLARE @Fim DATETIME 


SET @Inicio = '2012-02-18 10:34:09'
SET @Fim = '2012-02-18 10:50:09'


SELECT DATEDIFF(MINUTE, @INICIO, @FIM)

Coloque mais detalhes da estrutura da sua tabela e das possibilidades dos dados para que possamos ajudar com o script completo.

---

Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

São registros diferentes e não duram mais que 1 dia!!

O resultado que vc enviou é a média ou a diferença de entre início e fim?

Vou melhorar, pode acontecer de ter mais registros antes do fim do atendimento como ex:

joao 18/02/2012 10:50:09 finalizou atendimento

joao 18/02/2012 10:40:09 inseriu foto

joao 18/02/2012 10:36:09 inseriu documento

joao 18/02/2012 10:34:09 iniciou atendimento

Com isso irei precisar pegar o login que no caso é joão e a média de tempo que ele fez o atendimento.

Answer 3

Diego o exemplo da Mariana é a diferença entre a data de inicio e fim para obter a média vc poderia implementar na sua query:

select
Avg(Datediff(Minute, DataFim, DataInicio)) as Media
, NomeUsuario
from Tabela
group by NomeUsuario 

Answer 4

Diego o exemplo da Mariana é a diferença entre a data de inicio e fim para obter a média vc poderia implementar na sua query:

select
Avg(Datediff(Minute, DataFim, DataInicio)) as Media
, NomeUsuario
from Tabela
group by NomeUsuario 

E como fazer com vários registros como acima?

Answer 5

Vc quer uma query que no fim retorne a média de tempo do atendimento ou quer que em cada linha (apontamento) conste a média:

Poderia usar algo como isto:

declare @tab table ( NomeUsuario varchar(50), Titulo varchar(50), Data datetime )
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'finalizou atendimento', '2012-02-18 10:50:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'inseriu foto', '2012-02-18 10:40:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'inseriu documento', '2012-02-18 10:36:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'iniciou atendimento', '2012-02-18 10:34:09')

select
	DateDiff(Minute, ApontamentoAnterior.Data, tab.Data) as Minutos
	, tab.NomeUsuario
	, tab.Titulo
	, tab.Data
from @tab tab
	-- Obtem o apontamento anterior
	outer apply (
		select top 1 *
		from @tab tab2
		where
			tab2.NomeUsuario = tab.NomeUsuario
			and tab2.Data < tab.Data
		order by tab2.Data desc
	) ApontamentoAnterior
order by tab.Data Desc

select
	Avg(T.Minutos) as MediaGeral
from (
	select
		DateDiff(Minute, ApontamentoAnterior.Data, tab.Data) as Minutos
		, tab.NomeUsuario
		, tab.Titulo
		, tab.Data
	from @tab tab
		-- Obtem o apontamento anterior
		outer apply (
			select top 1 *
			from @tab tab2
			where
				tab2.NomeUsuario = tab.NomeUsuario
				and tab2.Data < tab.Data
			order by tab2.Data desc
		) ApontamentoAnterior
) T

Na query não tem nenhum filtro mas vc colocaria o filtro pela chave da tabela tipo IdChamado por exemplo.

Answer 6

Diego, 

Se você quer a média de cada ação dentro de um atendimento, acredito que o código abaixo te ajude. Fiz uma tabela temporária para simular os resultados.

Obs. ele só funciona a partir do SQL 2008 por conter CTE).

CREATE TABLE #TEMPCLIENTE
(
	NOME VARCHAR(100),
	IDAtendimento INT,
	DescricaoAcao VARCHAR(1000),
	Data DATETIME
)


INSERT INTO #TEMPCLIENTE
VALUES ('JOÃO', 1, 'iniciou atendimento', '2012-02-18 10:34:09'),
('JOÃO', 1, 'inseriu documento', '2012-02-18 10:36:09'),
('JOÃO', 1, 'inseriu foto', '2012-02-18 10:40:09'),
('JOÃO', 1, 'finalizou atendimento', '2012-02-18 10:50:09'),
 ('MARIA', 2, 'iniciou atendimento', '2012-02-20 11:10:00'),
('MARIA', 2, 'inseriu documento', '2012-02-20 11:36:00'),
('MARIA', 2, 'Finalizou Atendimento', '2012-02-20 12:40:00')




;WITH Calculo (IDLinha, nome, Data, IDAtendimento)
AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY NOME ORDER BY Data ASC), Nome, Data, IDAtendimento
	FROM #TEMPCLIENTE
	WHERE NOME = 'Maria'

)
SELECT c1.Nome,  SUM(DATEDIFF(MINUTE, C1.DATA, C2.DATA))/(sELECT COUNT(*) FROM Calculo)
FROM Calculo as C1
inner JOIN Calculo as C2 on C2.IDLinha = C1.IdLinha + 1 AND c1.IDAtendimento = c2.IDAtendimento
GROUP BY c1.NOME




drop table #TEMPCLIENTE

---

Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

Answer 7

Diego, 

Se você quer a média de cada ação dentro de um atendimento, acredito que o código abaixo te ajude. Fiz uma tabela temporária para simular os resultados.

Obs. ele só funciona a partir do SQL 2008 por conter CTE).

CREATE TABLE #TEMPCLIENTE
(
	NOME VARCHAR(100),
	IDAtendimento INT,
	DescricaoAcao VARCHAR(1000),
	Data DATETIME
)


INSERT INTO #TEMPCLIENTE
VALUES ('JOÃO', 1, 'iniciou atendimento', '2012-02-18 10:34:09'),
('JOÃO', 1, 'inseriu documento', '2012-02-18 10:36:09'),
('JOÃO', 1, 'inseriu foto', '2012-02-18 10:40:09'),
('JOÃO', 1, 'finalizou atendimento', '2012-02-18 10:50:09'),
 ('MARIA', 2, 'iniciou atendimento', '2012-02-20 11:10:00'),
('MARIA', 2, 'inseriu documento', '2012-02-20 11:36:00'),
('MARIA', 2, 'Finalizou Atendimento', '2012-02-20 12:40:00')




;WITH Calculo (IDLinha, nome, Data, IDAtendimento)
AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY NOME ORDER BY Data ASC), Nome, Data, IDAtendimento
	FROM #TEMPCLIENTE
	WHERE NOME = 'Maria'

)
SELECT c1.Nome,  SUM(DATEDIFF(MINUTE, C1.DATA, C2.DATA))/(sELECT COUNT(*) FROM Calculo)
FROM Calculo as C1
inner JOIN Calculo as C2 on C2.IDLinha = C1.IdLinha + 1 AND c1.IDAtendimento = c2.IDAtendimento
GROUP BY c1.NOME




drop table #TEMPCLIENTE

---

Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

Fiz a sua simulação aqui e tive 2 resultados distintos.

Se usar o seu script "maria" tem 30m de média, mas se retiro o filtro "maria", "maria" tem 12m e "joao" tem 2m

Answer 8

Diego,

Se você retirar o filtro a query não irá servir. O resultado estará errado.

Eu fiz considerando que você vai passar o nome do atendente. Se não for dessa maneira, a query precisará ser alterada.
Qual é a sua necessidade nesse ponto?

Não sei se é o melhor caminho, mas...

CREATE TABLE #TEMPCLIENTE
(
	NOME VARCHAR(100),
	IDAtendimento INT,
	DescricaoAcao VARCHAR(1000),
	Data DATETIME
)


INSERT INTO #TEMPCLIENTE
VALUES ('JOÃO', 1, 'iniciou atendimento', '2012-02-18 10:34:09'),
('JOÃO', 1, 'inseriu documento', '2012-02-18 10:36:09'),
('JOÃO', 1, 'inseriu foto', '2012-02-18 10:40:09'),
('JOÃO', 1, 'finalizou atendimento', '2012-02-18 10:50:09'),
 ('MARIA', 2, 'iniciou atendimento', '2012-02-20 11:10:00'),
('MARIA', 2, 'inseriu documento', '2012-02-20 11:36:00'),
('MARIA', 2, 'Finalizou Atendimento', '2012-02-20 12:40:00')




;WITH Calculo (IDLinha, nome, Data, IDAtendimento)
AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY NOME ORDER BY Data ASC), Nome, Data, IDAtendimento
	FROM #TEMPCLIENTE
)
SELECT  c1.nome,  SUM(DATEDIFF(MINUTE, C1.DATA, C2.DATA))/(sELECT COUNT(*) FROM Calculo where idatendimento = c1.idatendimento)
FROM Calculo as C1
inner JOIN Calculo as C2 on C2.IDLinha = C1.IdLinha + 1 AND c1.IDAtendimento = c2.IDAtendimento
GROUP BY c1.NOME, c1.idatendimento


drop table #TEMPCLIENTE

Essa query retorna 4 para João e 30 para Maria que é a média do tempo de cada etapa do atendimento.
João tem os tempos de 2, 4 e 10 minutos (entre cada etapa de atendimento). Então 16/4 = 4 minuto

Maria tem os tempos de 26 e 64 minutos. Logo 90/3 = 30 minutos.

---

Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

Answer 9

A sua resposta e a da lucas deram certo!!

Entretanto vamos enriquecer o tópico?

Se fosse em dias diferentes e também exibindo qual registro a pessoa mais leva tempo pra avançar até o término do atendimento? Seria possível?

Answer 10

Diego para fazer isto no caso ao invés de usar o datediff vc pode tentar utilizar o datepart e realizar so uma conta de datamaior - datamenor:

declare @data datetime = '2015-02-20 13:56:00.000'
declare @data2 datetime = '2015-02-22 15:30:00.000'
select 
	datepart(DAY, @data2 - @data) as Dia
	, datepart(Hour, @data2 - @data) as Horas
	, datepart(Minute, @data2 - @data) as Minutos
	, @data2 - @data as [@data2 - @data]

O resultado vai ser:

Dia         Horas       Minutos     @data2 - @data
----------- ----------- ----------- -----------------------
3             1             34          1900-01-03 01:34:00.000

Answer 11

Diego para fazer isto no caso ao invés de usar o datediff vc pode tentar utilizar o datepart e realizar so uma conta de datamaior - datamenor:

declare @data datetime = '2015-02-20 13:56:00.000'
declare @data2 datetime = '2015-02-22 15:30:00.000'
select 
	datepart(DAY, @data2 - @data) as Dia
	, datepart(Hour, @data2 - @data) as Horas
	, datepart(Minute, @data2 - @data) as Minutos
	, @data2 - @data as [@data2 - @data]

O resultado vai ser:

Dia         Horas       Minutos     @data2 - @data
----------- ----------- ----------- -----------------------
3             1             34          1900-01-03 01:34:00.000

Lucas tem como enviar no formato do seu script la de cima que ta certinho?

Answer 12

Diego ficaria assim:

declare @tab table ( NomeUsuario varchar(50), Titulo varchar(50), Data datetime )
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'finalizou atendimento', '2012-02-19 10:50:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'inseriu foto', '2012-02-18 10:40:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'inseriu documento', '2012-02-18 10:36:09')
insert into @tab (NomeUsuario, Titulo, Data) values ('João', 'iniciou atendimento', '2012-02-18 10:34:09')

select
	datepart(day, tab.Data - ApontamentoAnterior.Data) as DiasNovoFormato
	, datepart(minute, tab.Data - ApontamentoAnterior.Data) as MinutosNovoFormato
	, tab.NomeUsuario
	, tab.Titulo
	, tab.Data
from @tab tab
	-- Obtem o apontamento anterior
	outer apply (
		select top 1 *
		from @tab tab2
		where
			tab2.NomeUsuario = tab.NomeUsuario
			and tab2.Data < tab.Data
		order by tab2.Data desc
	) ApontamentoAnterior
order by tab.Data Desc

select
	Avg(T.MinutosNovoFormato) as MediaGeralMinutosNovoFormato
	, Avg(T.DiasNovoFormato) as MediaGeralDiasNovoFormato
from (
	select
		datepart(day, tab.Data - ApontamentoAnterior.Data) as DiasNovoFormato
		, datepart(minute, tab.Data - ApontamentoAnterior.Data) as MinutosNovoFormato
		, tab.NomeUsuario
		, tab.Titulo
		, tab.Data
	from @tab tab
		-- Obtem o apontamento anterior
		outer apply (
			select top 1 *
			from @tab tab2
			where
				tab2.NomeUsuario = tab.NomeUsuario
				and tab2.Data < tab.Data
			order by tab2.Data desc
		) ApontamentoAnterior
) T

Answer 13

Lucas, faltou só a média por dia e login no segundo select.

Fiz aqui mas pegou os mesmos resultados do primeiro select.