none
converter campo char para horas e somar as horas SQL SERVER RRS feed

  • Pergunta

  • Galera tenho um problema 

    existe uma tabelas q possui um campo hora mas esta em char (fazer o q ) rsrs e preciso converter este campo para hora e totalizar este toral de horas 

    a tabela é assim

    OP  time data

    login  9:56.17     09/01/2013

    login                 9:59.17   10/01/2013

    login  10:05.50 18/01/2013

    logout 10:09.17     09/01/2013

    logout              12:59.17   10/01/2013

    logout  15:05.50 18/01/2013

    gostaria tivesse um total dos logins e tbm dos logouts

            

    quinta-feira, 21 de março de 2013 14:24

Respostas

  • Brown,

    Segue um exemplo:

    IF(OBJECT_ID('TabelaHoraBrown') IS NOT NULL)
    	DROP TABLE TabelaHoraBrown
    
    CREATE TABLE TabelaHoraBrown
    (
    	Id_Screen VARCHAR(100),
    	MOV_DATE VARCHAR(100),
    	MOV_TIME VARCHAR(100),
    	Operation VARCHAR(100)
    )
    
    INSERT INTO TabelaHoraBrown(Id_Screen, MOV_DATE, MOV_TIME, Operation)
    VALUES
    ('Tela_01','20.3.2013','9:56.17','LOGIN')
    ,('Tela_02','21.3.2013','11:56.23','LOGIN')
    ,('Tela_01','20.3.2013','10:11.40','LOGOUT')
    ,('Tela_03','21.3.2013','12:56.17','LOGIN')
    ,('Tela_04','21.3.2013','11:56.23','LOGIN')
    ,('Tela_02','20.3.2013','16:11.40','LOGOUT')
    ,('Tela_03','21.3.2013','15:11.40','LOGOUT')
    ,('Tela_04','21.3.2013','17:11.40','LOGOUT')
    ,('Tela_01','23.3.2013','9:56.17','LOGIN')
    ,('Tela_01','25.3.2013','11:56.23','LOGOUT')
    GO
    
    --Função de tratativa
    IF(OBJECT_ID('IntToMinutes') IS NOT NULL)
    	DROP FUNCTION IntToMinutes
    GO
    CREATE FUNCTION [dbo].[IntToMinutes]
    (
        @m smallint 
    )  
    RETURNS nvarchar(5)
    AS  
    BEGIN
        DECLARE @c datetime
        select @c = dateadd(mi,@m,'00:00')       
        RETURN convert(nvarchar(5), @c, 108)   
    END
    GO
    --SELECT FINAL
    SET DATEFORMAT DMY
    
    ;WITH CTE(Tela, Data, Hora, Operacao, RNK)
    AS
    (
    	SELECT Id_Screen, MOV_DATE, MOV_TIME, Operation, ROW_NUMBER() OVER(PARTITION BY Id_Screen, Operation ORDER BY MOV_DATE + MOV_TIME) As RNK
    	FROM TabelaHoraBrown
    )
    SELECT
    	a.Tela,
    	a.Data,
    	[dbo].[IntToMinutes](DATEDIFF(MINUTE, a.Hora, (SELECT b.Hora FROM CTE b WHERE b.RNK = a.RNK AND b.Tela = a.Tela AND b.Operacao LIKE 'LOGOUT'))) As DiferencaLogada
    FROM CTE a
    WHERE a.Operacao LIKE 'LOGIN'


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marcado como Resposta Brown11 quinta-feira, 21 de março de 2013 19:09
    quinta-feira, 21 de março de 2013 17:30
    Moderador
  • Brown,

    Fico feliz que esteja batendo.

    1 - Essa alteração não fará trazer os secundos, para isso é necessario outra função;

    2 - Utilize um group by grouping sets para isso, é a melhor opção.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marcado como Resposta Brown11 quinta-feira, 21 de março de 2013 19:36
    quinta-feira, 21 de março de 2013 19:16
    Moderador

Todas as Respostas

  • Brown11, Bom dia!

    Tente desta maneira:

    IF(OBJECT_ID('TabelaHoraBrown') IS NOT NULL)
    	DROP TABLE TabelaHoraBrown
    
    CREATE TABLE TabelaHoraBrown
    (
    	OP	INT,
    	[time] VARCHAR(100),
    	[date] VARCHAR(100)
    )
    
    INSERT INTO TabelaHoraBrown(OP, time, date)
    VALUES
    (1, '1:00:36', '09/01/2013'),
    (1, '1:00:31', '10/01/2013'),
    (2, '1:00:32', '11/01/2013'),
    (2, '1:00:42', '12/01/2013'),
    (2, '1:00:43', '13/01/2013')
    
    --SELECT FINAL
    SET DATEFORMAT DMY
    SELECT OP, CAST(DATEADD(MILLISECOND,SUM(DATEDIFF(MILLISECOND,0,CAST([time] AS DATETIME))),0) AS TIME)--CAST([time]] AS time)
    FROM TabelaHoraBrown
    GROUP BY OP
    


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 21 de março de 2013 14:31
    Moderador
  • Fala Fabrizzio tudo blz

    Cara apresentou este erro

    Mensagem 8115, Nível 16, Estado 2, Linha 3
    Erro de estouro aritmético ao converter expression no tipo de dados int.

    Lembrando q a data estava em char qdo ele é convertida para datetime ela aparece assim: 1900-01-01 09:56:00.170

    e na tabela é é lançada assim : 09:56.00 

    teríamos q matar esta data 1900-01-01???

    quinta-feira, 21 de março de 2013 14:51
  • Brown,

    Poste uma estrutura formatada corretamente de seus dados, o forum desformatou tudo e não estou conseguindo entender efetivamente como os dados estão.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 21 de março de 2013 14:53
    Moderador
  • OPERATION MOV_DATE MOV_TIME LOGIN 01.3.2013 9:56.17 LOGOUT 05.3.2013 10:11.40

    LOGIN 01.3.2013 11:56.17 LOGOUT 05.3.2013 19:11.40

    LOGIN 21.3.2013 10:56.17 LOGOUT 27.3.2013 19:11.40

    LOGIN 21.3.2013 11:59.17 LOGOUT 27.3.2013 19:11.40

    lembrando tudo esta como char


    quinta-feira, 21 de março de 2013 15:04
  • Brown,

    O que voce precisa é da diferença de horario entre o login e o logout? Caso positivo, qual é a chave? Algo como o id do usuario por exemplo...


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 21 de março de 2013 15:50
    Moderador
  • total e a diferença 

    entre o login e logout 

    id da tela é a chave

    quinta-feira, 21 de março de 2013 15:54
  • Brown?

    Qual id da tela? Coloque na estrutura todos os campos envolvidos no select se possivel.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 21 de março de 2013 16:01
    Moderador
  • ID_SCREEN		MOV_DATE	MOV_TIME	OPERATION	
    Tela_01     	20.3.2013   9:56.17     LOGIN    
    Tela_02         21.3.2013   11:56.23     LOGIN
    Tela_01         20.3.2013   10:11.40    LOGOUT 
    Tela_03     	21.3.2013   12:56.17     LOGIN    
    Tela_04         21.3.2013   11:56.23     LOGIN
    Tela_02         20.3.2013   16:11.40    LOGOUT
    Tela_03         21.3.2013   15:11.40    LOGOUT
    Tela_04         21.3.2013   17:11.40    LOGOUT

    Segue ai Fabrizzio

    Sendo q não existe chave primária nesta tabela 

    os campos envolvidos no select 

    seria o total de horas no login 

    e o total de horas do logout 

    lembrando q todos os campos estão com o tipo char

    a ideia e ter um sum por operação

    quinta-feira, 21 de março de 2013 17:03
  • Brown,

    Só para entender, na verdade voce quer a soma de todos os logins e logouts ou voce quer a soma das diferenças entre login e logout? Veja que sao coisas distintas!


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    quinta-feira, 21 de março de 2013 17:06
    Moderador
  • Eu sei Fabrizzio

    na verdade eu vou querer saber o tempo q cada tela ficou logada

    essa diferença do login da tela e do logout da mesma tela 

    por exemplo neste mês a tela_01 ficou logada de 9:00 horas do dia 20/03/2013 e teve um logout as 10:00 do dia 21/03/2013 ou seja ela ficou logada 25 horas

    Mas tbm vou querer o total de tempo de todas as telas

    Realmente confundi e me expressei mal 

    quinta-feira, 21 de março de 2013 17:19
  • Brown,

    Segue um exemplo:

    IF(OBJECT_ID('TabelaHoraBrown') IS NOT NULL)
    	DROP TABLE TabelaHoraBrown
    
    CREATE TABLE TabelaHoraBrown
    (
    	Id_Screen VARCHAR(100),
    	MOV_DATE VARCHAR(100),
    	MOV_TIME VARCHAR(100),
    	Operation VARCHAR(100)
    )
    
    INSERT INTO TabelaHoraBrown(Id_Screen, MOV_DATE, MOV_TIME, Operation)
    VALUES
    ('Tela_01','20.3.2013','9:56.17','LOGIN')
    ,('Tela_02','21.3.2013','11:56.23','LOGIN')
    ,('Tela_01','20.3.2013','10:11.40','LOGOUT')
    ,('Tela_03','21.3.2013','12:56.17','LOGIN')
    ,('Tela_04','21.3.2013','11:56.23','LOGIN')
    ,('Tela_02','20.3.2013','16:11.40','LOGOUT')
    ,('Tela_03','21.3.2013','15:11.40','LOGOUT')
    ,('Tela_04','21.3.2013','17:11.40','LOGOUT')
    ,('Tela_01','23.3.2013','9:56.17','LOGIN')
    ,('Tela_01','25.3.2013','11:56.23','LOGOUT')
    GO
    
    --Função de tratativa
    IF(OBJECT_ID('IntToMinutes') IS NOT NULL)
    	DROP FUNCTION IntToMinutes
    GO
    CREATE FUNCTION [dbo].[IntToMinutes]
    (
        @m smallint 
    )  
    RETURNS nvarchar(5)
    AS  
    BEGIN
        DECLARE @c datetime
        select @c = dateadd(mi,@m,'00:00')       
        RETURN convert(nvarchar(5), @c, 108)   
    END
    GO
    --SELECT FINAL
    SET DATEFORMAT DMY
    
    ;WITH CTE(Tela, Data, Hora, Operacao, RNK)
    AS
    (
    	SELECT Id_Screen, MOV_DATE, MOV_TIME, Operation, ROW_NUMBER() OVER(PARTITION BY Id_Screen, Operation ORDER BY MOV_DATE + MOV_TIME) As RNK
    	FROM TabelaHoraBrown
    )
    SELECT
    	a.Tela,
    	a.Data,
    	[dbo].[IntToMinutes](DATEDIFF(MINUTE, a.Hora, (SELECT b.Hora FROM CTE b WHERE b.RNK = a.RNK AND b.Tela = a.Tela AND b.Operacao LIKE 'LOGOUT'))) As DiferencaLogada
    FROM CTE a
    WHERE a.Operacao LIKE 'LOGIN'


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marcado como Resposta Brown11 quinta-feira, 21 de março de 2013 19:09
    quinta-feira, 21 de março de 2013 17:30
    Moderador
  • Fabrizzio estou testando aqui com meus dados e acho q esta batendo legal

    uma pergunta vc esta trazendo a hora e os minutos certo?

    eu poderia alterar para :

    select @c = dateadd(mi,@m,'00:00:00')       
        RETURN convert(nvarchar(7), @c, 108) 

    e trazer os segundos tbm?

    e outra eu poderia totalizar por tela tbm ? todas as DiferencaLogada da tela_01 por exemplo somadas?

    quinta-feira, 21 de março de 2013 19:13
  • Brown,

    Fico feliz que esteja batendo.

    1 - Essa alteração não fará trazer os secundos, para isso é necessario outra função;

    2 - Utilize um group by grouping sets para isso, é a melhor opção.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    • Marcado como Resposta Brown11 quinta-feira, 21 de março de 2013 19:36
    quinta-feira, 21 de março de 2013 19:16
    Moderador
  • Valeu Fabrizzio 

    Brigadão cara 


    quinta-feira, 21 de março de 2013 19:37