Split between values - Query SQL Server 2012

Question

Split between values - Query SQL Server 2012

Navigation

#1 by (1 votes)

0

Galera, in the query below, is as follows.

Ano  Mes  Horas  total_horas
2018  1    107    766 H
2018  1    225    766 H

So, I want you to bring in another column the division of the column "Hours" by the column "total_horas", bringing this way.

Ano  Mes  Horas  total_horas    media
2018  1    107    766 H        0,139996
2018  1    225    766 H        0,293735

Follow the query below.

select
t1.Ano,t1.Mês,t1.horas,t2.total_horas
from
(select sum(f.C06) horas, DATEPART(month, TarFechamento) Mês, DATEPART(YEAR,     TarFechamento) Ano, FC.Valor Tipo 
 from  Tarefa t 
    INNER JOIN Usuario u on t.UsuIDResponsavel = u.UsuID and u.UsuIDGrupo = 7 
    INNER JOIN FRM_10 F ON F.TarefaID = T.TarID AND F.C04 IS NOT NULL
    INNER JOIN FormularioCampoValor FC ON FC.ValorID = F.C04
    where t.ProID in (9,3,12,7,10,4,5,2) AND T.TarTipID NOT IN (1048,1049,1050) and datepart(year,tarfechamento)>= 2018 and t.TarStatus = 9
 group by DATEPART(YEAR, TarFechamento),  datepart(month,TarFechamento),     FC.Valor)t1

left join

(select concat(cast(sum(f.C06) as varchar(100)),' ','H')  total_horas,     DATEPART(month, TarFechamento) Mês, DATEPART(YEAR, TarFechamento) Ano
 from  Tarefa t 
    INNER JOIN Usuario u on t.UsuIDResponsavel = u.UsuID and u.UsuIDGrupo = 7 
    INNER JOIN FRM_10 F ON F.TarefaID = T.TarID AND F.C04 IS NOT NULL
    INNER JOIN FormularioCampoValor FC ON FC.ValorID = F.C04
    where t.ProID in (9,3,12,7,10,4,5,2) AND T.TarTipID NOT IN (1048,1049,1050) and datepart(year,tarfechamento)>= 2018 and t.TarStatus = 9
    group by DATEPART(YEAR, TarFechamento),  datepart(month,TarFechamento))t2 on t1.Ano = t2.Ano and t1.Mês = t2.Mês

NOTE: I tried to perform in the external query above this way, but without success ..

select
t1.Ano,t1.Mês,t1.horas / t2.total_horas

sql sql-server query

asked by anonymous 17.01.2018 / 14:15

1 answer

Application for login to another site (Facebook), error "InvalidSubmitError" Submit POST requests by browser or other program

score 1 · Accepted Answer

When you use

select t1.Ano,t1.Mês,t1.horas / t2.total_horas

for ti.horas and t2.total_horas be integers we will have as answer integer values ie

Ano  Mes  Horas  total_horas    media
2018  1    107    766 H        0
2018  1    225    766 H        0

Then divide it as follows to get results in float

SELECT t1.Ano,t1.Mês,CAST(t1.horas AS float) / CAST(t2.total_horas AS float)

As total_hours has to be int to be able to run the split replace

concat(cast(sum(f.C06) as varchar(100)),' ','H')  total_horas

by

sum (f.C06) total_hours

and if you want concat o add no select:

SELECT t1.Ano,t1.Mês,concat(cast(t2.total_horas as varchar(100)),' ','H')  total_horas,CAST(t1.horas AS float) / CAST(t2.total_horas AS float)