Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
E ai galera, tenho uma duvida aqui que nao consigo solucionar.Estou trabalhando com o SQL Server 2000, importei um arquivo *.txt separado por tabulacao (o aquivo tem mais de 200 mil registros, no excel trava direto) que tem uma coluna com as informacoes abaixo e seus respectivos significados:Exemplos746585 = 7 horas, 46 minutos e 58.5 segundos038335 = 38 minutos e 33.5 segundos005215 = 5 minutos e 21.5 segundos000455 = 45.5 segundos000070 = 7 segundos000065 = 6.5 segundosComo faco eu para transformar:746585 em 466.975 minutos38335 = 38.558 minutos5215 = 5.358 minutos455 = 0.758 minutos70 =0.166 minutos65 = 0.1083 minutosOu seja, converter tais valores em minutos?Desde ja agradeco.Andre Mori
E ai RAVATE, grato pela ajuda. Tem que ser brazuca mesmo pra resolver os pepinos, eu estou com este mesmo issue em uns 10 outros forums por ai pendurados, consegui 2 respostas com o CAST mas so o seu funcionou ate agora.
Eu testei o primeiro CODE e funcionou muito bem, calcula perfeitamente. Ja no segundo CODE eu nao consegui fazer funcionar (provvelmente porque eu nao sei faze-lo funcionar).
Desculpe a minha ignorancia, eh que eu sou novo no SQL ainda.
Olha o que eu fiz:
SELECT CAST((CAST(LEFT(TIME,1) AS DECIMAL(8,3))*60) + CAST(SUBSTRING(TIME,2,2) AS DECIMAL(8,3)) + ((CAST(RIGHT(TIME,3) AS DECIMAL(8,3))/10)/60) AS DECIMAL(8,3))
Resultado:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'TIME'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'TIME'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'TIME'.
Onde entra o nome da tabela neste caso?
Eu tenho disponivel um arquivo de amostra no link http://www.aga.cc/download/sample.txt , eu preciso fazer mais ou menos este calculo:
select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#
Ou seja, calcular os totais TIME e PRICE de cada Access# e dividir o preco pelo tempo para saber quanto custou o minuto por acesso.
Nao faco a minima ideia como indicar que no codigo SET @TIMESTAMP , o valor '000455' eh a coluna TIME, nao sei como ensinar o caminho para que ele possa buscar os dados nesta coluna.
Mais uma vez obrigado pela ajuda.
Andre Mori
Baseado no sample que você colocou em sua página.
Importei os campos Access#, Time e Cost nos formatos VARCHAR(11), INT e INT, respectivamente.
Elaborei a seguinte query para retornar o seu resultado:
SELECT access#, SUM(time) AS TIME, SUM(cost) AS PRICE, CAST((SUM(cost) / SUM(((CAST(substring(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) + CAST(substring(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) + (CAST(substring(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60))) AS DECIMAL(8,2)) AS MONEY FROM sample GROUP BY access#Retorno:
>
access# TIME PRICE MONEY
----------- ----------- ----------- ----------
0120-446631 103065 5188 79.68
0120-654481 103890 5364 81.50
0120-801214 6465 1050 119.66
(3 row(s) affected)
E ai RAVATE, eu rodei o codigo e parece que algo nao funcionou. se voce der um select * from sample o resultado sera:access# time price Exemplos0120-446631 195 75 (19.5 seg ou 0.325 minutos)0120-446631 100530 4575 (1 hora e 53 segundos ou 60.88 minutos)0120-446631 190 880120-446631 395 750120-801214 1245 150 (1 minuto e 24.5 segundos ou 1.4083 minutos)0120-801214 425 750120-801214 335 750120-801214 350 750120-801214 340 750120-654481 325 750120-654481 410 750120-654481 195 750120-654481 375 750120-654481 65 750120-654481 100520 45750120-654481 265 750120-654481 1035 880120-654481 290 880120-654481 165 880120-654481 245 750120-801214 265 750120-801214 170 750120-801214 2490 3000120-801214 395 750120-801214 450 750120-446631 400 750120-446631 395 750120-446631 395 750120-446631 425 750120-446631 140 75TOTAL 139.70 minutos ao inves do valor 213420 resultado do codigo que voce elaborouSe voce seguir a regra no campo time (exemplo: 106530 = 1 hora, 6 segundos e 53 segundos), a soma dos campos acima deveria ser:Total = 139.70 minutos, dos quais:Access# Time0120-446631 64.880120-801214 8.780120-654481 65.82Rodando o seu codigo, o resultado eh o seguinte:Access# Time0120-446631 1030650120-654481 1038900120-801214 6465Ele esta somando o campos e agrupando em tipo de access# mas nao esta transformando os minutos, sera que estou fazendo algo errado?[]'s,
Você não está fazendo nada errado não.
Eu que achei que só deveria realizar a transformação pra minuto na coluna MONEY.
Aqui vai a correção para a coluna TIME também:
SELECT access#, CAST(SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) + CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) + (CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)60)) AS DECIMAL(8,2)) AS TIME, SUM(cost) AS PRICE, CAST((SUM(cost) / SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) + CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) + (CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)60))) AS DECIMAL(8,2)) AS MONEY FROM sampleGROUP BY access#
Grande RAVATE!Funcionou otimamente bem! Fiz algumas alteracoes e agora que voce ja fez o "grosso" do codigo eu vou adaptando-o as minhas necessidades (por exemplo o 0.1 na soma).Codigo Final:SELECT ŠeŽíƒT[ƒrƒX”Ô† AS FREE#, CAST(SUM(((CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-2, 3) AS DECIMAL)/10)/60) + CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-4, 2) AS INT) + (CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-6, 2) AS INT)60)) AS DECIMAL(8,2)) AS TIME, SUM(’ʘb—¿‹à‚P0.1) AS PRICE, CAST((SUM(’ʘb—¿‹à‚P0.1) / SUM(((CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-2, 3) AS DECIMAL)/10)/60) + CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-4, 2) AS INT) + (CAST(SUBSTRING(CAST(’ʘbŽžŠÔ AS CHAR(7)), LEN(’ʘbŽžŠÔ)-6, 2) AS INT)*60))) AS DECIMAL(8,2)) AS MONEYFROM cdr1_accessGROUP BY ŠeŽíƒT[ƒrƒX”Ô†ORDER BY ŠeŽíƒT[ƒrƒX”Ô† ASCO que eu levava 3 horas pra fazer antigamente agora nao levo mais do que 10 minutos (demorava porque o excel travava direto).Valeu!Mais uma vez obrigado!Andre Mori
Pelo seu exemplo, posso observar que as horas vão no máximo até 9.
Caso vá até 24, readequar as posições do LEFT e do SUBSTRING.
DECLARE @TIMESTAMP AS CHAR(6)DECLARE @HORA AS DECIMAL(8,3)DECLARE @MINUTO AS DECIMAL(8,3)DECLARE @SEGUNDO AS DECIMAL(8,3)DECLARE @TEMPO AS DECIMAL(8,3)SET @TIMESTAMP = '000455'SET @HORA = LEFT(@TIMESTAMP,1)SET @MINUTO = SUBSTRING(@TIMESTAMP,2,2)SET @SEGUNDO = RIGHT(@TIMESTAMP,3)SET @TEMPO = ((@HORA*60) + @MINUTO + ((@SEGUNDO/10)/60))PRINT @TEMPO
Ou então apenas um select que traga este resultado.
Substituir COLUNA pelo nome da sua coluna na tabela.
SELECT CAST((CAST(LEFT(COLUNA,1) AS DECIMAL(8,3))*60) + CAST(SUBSTRING(COLUNA,2,2) AS DECIMAL(8,3)) + ((CAST(RIGHT(COLUNA,3) AS DECIMAL(8,3))/10)/60) AS DECIMAL(8,3))