Conversion of SQL Server types

Question

Conversion of SQL Server types

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the following SELECT in SQL Server:

SELECT  
    th.CidadeCod,
    th.ContratoCod,
    CONVERT(DECIMAL(5,2), ( SUM(th.PlacasLidas)/SUM(th.Trafego) ) * 100) AS [IndiceOCR]
FROM 
    controle.TrafegoFaixaHora th 
INNER JOIN ssis.Contratos c ON th.ContratoCod = c.ContratoCod AND th.CidadeCod = c.CidadeCod
INNER JOIN controle.Equipamentos e ON th.ContratoCod = e.ContratoCod AND th.CidadeCod = e.CidadeCod
WHERE 
    c.FlagAtivo = 1 AND e.FlagOcr = 1
GROUP BY 
    th.CidadeCod,
    th.ContratoCod

But the error is returned:

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

What should I do?

sql sql-server

asked by anonymous 21.10.2016 / 13:34

2 answers

0

Change

 CONVERT(DECIMAL(5,2), ( SUM(th.PlacasLidas)/SUM(th.Trafego) ) * 100) AS [IndiceOCR]

for something like this:

CONVERT(DECIMAL(15,2), ( SUM(th.PlacasLidas)/SUM(th.Trafego) ) * 100) AS [IndiceOCR]

21.10.2016 / 13:46

Hosting a small javaweb software on jelastic localweb [closed] Two foreachs php - Duplicate data

score 1 · Accepted Answer

I solved it as follows:

SELECT  
        th.CidadeCod
        ,th.ContratoCod
        ,SUM(CONVERT(BIGINT,th.Trafego)) AS [trafego]
        ,SUM(CONVERT(BIGINT,th.PlacasLidas)) AS [placaslidas]
        ,( 
        CONVERT(DECIMAL(15,2), 
        CONVERT(DECIMAL(15,5), SUM(CONVERT(BIGINT,th.PlacasLidas))) / 
        CONVERT(DECIMAL(15,5), SUM(CONVERT(BIGINT,th.Trafego))) 
        *100 )) AS [IndiceOCR]
FROM controle.TrafegoData th 
INNER JOIN ssis.Contratos c ON th.ContratoCod = c.ContratoCod AND th.CidadeCod = c.CidadeCod
INNER JOIN controle.Equipamentos e ON th.ContratoCod = e.ContratoCod AND th.CidadeCod = e.CidadeCod

WHERE 
    th.Data BETWEEN DATEADD(DAY,-14,GETDATE()) AND DATEADD(DAY,-8,GETDATE())
    AND c.FlagAtivo = 1 
    AND e.FlagOcr = 1