none
Média em minutos RRS feed

  • Pergunta

  • 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

    terça-feira, 28 de abril de 2015 13:01

Respostas

  • 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.


    • Editado Lucas D Santos terça-feira, 28 de abril de 2015 14:08
    • Marcado como Resposta Diegodcr terça-feira, 28 de abril de 2015 16:49
    terça-feira, 28 de abril de 2015 13:58
  • 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 */


    • Editado Mariana Del Nero terça-feira, 28 de abril de 2015 14:59
    • Marcado como Resposta Diegodcr terça-feira, 28 de abril de 2015 16:49
    terça-feira, 28 de abril de 2015 14:51

Todas as Respostas

  • 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 */

    terça-feira, 28 de abril de 2015 13:13
  • 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.

    terça-feira, 28 de abril de 2015 13:20
  • 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 

    terça-feira, 28 de abril de 2015 13:49
  • 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?
    terça-feira, 28 de abril de 2015 13:55
  • 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.


    • Editado Lucas D Santos terça-feira, 28 de abril de 2015 14:08
    • Marcado como Resposta Diegodcr terça-feira, 28 de abril de 2015 16:49
    terça-feira, 28 de abril de 2015 13:58
  • 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 */

    terça-feira, 28 de abril de 2015 14:21
  • 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

    terça-feira, 28 de abril de 2015 14:43
  • 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 */


    • Editado Mariana Del Nero terça-feira, 28 de abril de 2015 14:59
    • Marcado como Resposta Diegodcr terça-feira, 28 de abril de 2015 16:49
    terça-feira, 28 de abril de 2015 14:51
  • 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?

    terça-feira, 28 de abril de 2015 16:51
  • 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


    terça-feira, 28 de abril de 2015 17:07
  • 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?

    terça-feira, 28 de abril de 2015 17:14
  • 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

    terça-feira, 28 de abril de 2015 17:31
  • Lucas, faltou só a média por dia e login no segundo select.

    Fiz aqui mas pegou os mesmos resultados do primeiro select.

    terça-feira, 28 de abril de 2015 18:00