Pra que gosta de quebrar a cuca!

[mori 0]

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

[RAVATE 0]

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))

[mori 0]

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

[RAVATE 0]

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)

[mori 0]

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,

[RAVATE 0]

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#

[mori 0]

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—¿‹à‚P*0.1) AS PRICE, CAST((SUM(’ʘb—¿‹à‚P*0.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