May 11, 2016 at 11:53 am
good afternoon ,
I'm hum code that brings me the values of the fields Table , however When I " * 100 / " it 's all me 100 % Being que value would be the amounts of ordserv . generated / ordserv.fechadas and then multiplied by 100 to give the percentage . Below follows the code :
SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES,
ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END) 'REALIZADAS'
FROM ORDSERV
WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
AND ORDSERV.STATORD='F'
AND ORDSERV.CODEMP=76
AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD)
GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
ORDER BY 1,2
Result:
Mouth Mouth_1 mouth_year conducted
--- ----- ------- ----------
3 2016 mar/16 489
4 2016 abr/16 659
This is the result that it gives me values are correct, total performed .
SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES,
ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
COUNT(*) 'GERADAS'
FROM ORDSERV
WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
AND ORDSERV.STATORD='F'
AND ORDSERV.CODEMP=76
GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
ORDER BY 1,2
Result:
Mouth Mouth_1 mouth_year conducted
--- ----- ------- -------
3 2016 mar/16 514
4 2016 abr/16 707
This is the result that it gives me values is correct , total generated.
When I ask him he gives me a percentage me back everything 100 %
SELECT DATEPART(MM, ORDSERV.DATPRO2) MES, DATEPART(YY, ORDSERV.DATPRO2) MES,
ENGEMAN.MESABREV(ORDSERV.DATPRO2)+'/'+SUBSTRING(CAST(DATEPART(YY, ORDSERV.DATPRO2) AS VARCHAR),3,2) MES_ANO,
SUM(CASE WHEN ORDSERV.STATORD='F' THEN 1 ELSE 0 END)*100/COUNT(*) AS PORCENTAGEM
FROM ORDSERV
WHERE ORDSERV.DATPRO2 BETWEEN '01/03/2016' AND '30/04/2016'
AND ORDSERV.STATORD='F'
AND ORDSERV.CODEMP=76
AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD)
GROUP BY DATEPART(MM, ORDSERV.DATPRO2),ENGEMAN.MESABREV(ORDSERV.DATPRO2), DATEPART(YYYY, ORDSERV.DATPRO2)
ORDER BY 1,2
Result:
Mouth Mouth_1 mouth_year conducted
--- ----- ------- -----------
3 2016 mar/16 100
4 2016 abr/16 100
I think the error is in the condition "AND 'S' IN (SELECT REGSERV.EXECUTADO FROM REGSERV WHERE REGSERV.CODEMP=ORDSERV.CODEMP AND REGSERV.CODORD=ORDSERV.CODORD) " ,
but it is with him that shot the table results REGSERV
and bring to the ORDSERV
giving me all those undertaken .
I ask your help because I'm already one week trying to solve it and nothing . Any help is welcome . I thank you all.
May 11, 2016 at 1:31 pm
Most of the people here don't speak Portuguese. You might have better luck if you posted your question in English.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 11, 2016 at 2:17 pm
code formatted for clarity and the incorrect alias fixed for year part of date
Answer in english even though I am Portuguese so others may benefit of the answer if it proves correct.
Se precisares que faça comentários em Português diz.
select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, sum(case when ordserv.statord='f' then 1 else 0 end) 'realizadas'
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
and 'S' in (select regserv.executado
from regserv
where regserv.codemp=ordserv.codemp
and regserv.codord=ordserv.codord)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
order by 1,2
select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, count(*) 'geradas'
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
order by 1,2
select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, sum(case when ordserv.statord='f' then 1 else 0 end) * 100 / count(*) as porcentagem
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
and 'S' in (select regserv.executado
from regserv
where regserv.codemp = ordserv.codemp
and regserv.codord = ordserv.codord)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
order by 1,2
Now from what you say on your description of what you need I think your 3rd sql is incorrect as the "count(*)" you have will not be the global one but the one related only to the orders "realizadas" (e.g. where regserv.executado = 'S')
below is what I think you need
select t.mes
, t.ano
, t.realizadas
, total.total_ordens_geradas
, convert(decimal(5,2), round(t.realizadas / convert(decimal(20,4), total.total_ordens_geradas) * 100 ,2)) as porcentagem
from (select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, sum(case when ordserv.statord='f' then 1 else 0 end) as realizadas
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
and 'S' in (select regserv.executado
from regserv
where regserv.codemp = ordserv.codemp
and regserv.codord = ordserv.codord)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
) t
outer apply (select count(*) as total_ordens_geradas
from ordserv as os2
where os2.datpro2 between '01/03/2016' and '30/04/2016'
and os2.statord='F'
and os2.codemp=76
and t.ano = datepart(mm, os2.datpro2)
and t.ano = datepart(yy, os2.datpro2)
-- I do not think the call to the function is required - but up to you to see if it is or not.
-- and t.mes_ano = engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2)
) total
order by 1,2
May 12, 2016 at 5:46 am
First Thanks for the support. The last query you posted the result was that,
not from the percentage and not the total number of orders generated:
mes ano realizadas total_ordens_geradas porcentagem
--- ---- ---------- -------------------- -----------
3 2016 489 0
4 2016 659 0
I wonder if I have to use the other 3 previous queries to somatize with the latest . I thank your attention.
May 12, 2016 at 6:54 am
Good morning Frederick
Getting the results to your query . But the results came repeated , equal to the actual percentage and another percentage with wrong .
mes ano realizadas total_ordens_geradas porcentagem
--- ---- ---------- -------------------- -----------
3 2016 489 514 95,14
3 2016 489 707 69,17
4 2016 659 514 128,21
4 2016 659 707 93,21
I am searching here because most still have not found it because of duplication. But if you can , thank you again for your support. Thank you very much.
May 12, 2016 at 7:00 am
I made a change so your code to generate the results follows :
select t.mes
, t.ano
, t.realizadas
, total.total_ordens_geradas
, convert(decimal(5,2), round(t.realizadas / convert(decimal(20,4), total.total_ordens_geradas) * 100 ,2)) as porcentagem
from (select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, sum(case when ordserv.statord='f' then 1 else 0 end) as realizadas
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
and 'S' in (select regserv.executado
from regserv
where regserv.codemp = ordserv.codemp
and regserv.codord = ordserv.codord)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
) t
outer apply (select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
,count(*) as total_ordens_geradas
from ordserv
where ordserv.datpro2 between '01/03/2016' and '30/04/2016'
and ordserv.statord='F'
and ordserv.codemp=76
--and t.ano = datepart(mm, ordserv.datpro2)
--and t.ano = datepart(yy, ordserv.datpro2)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
-- I do not think the call to the function is required - but up to you to see if it is or not.
-- and t.mes_ano = engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2)
) total
order by 1,2
May 12, 2016 at 7:02 am
pinheiroinfo (5/12/2016)
I made a change so your code to generate the results follows :
Please use IFCode tags in future. It makes things very much easier to read.
SELECT t.mes
, t.ano
, t.realizadas
, total.total_ordens_geradas
, CONVERT(DECIMAL(5, 2), ROUND(t.realizadas / CONVERT(DECIMAL(20, 4), total.total_ordens_geradas) * 100, 2)) AS porcentagem
FROM (SELECT DATEPART(mm, ordserv.datpro2) mes
, DATEPART(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + SUBSTRING(CAST(DATEPART(yy, ordserv.datpro2) AS VARCHAR), 3,
2) mes_ano
, SUM(CASE WHEN ordserv.statord = 'f' THEN 1
ELSE 0
END) AS realizadas
FROM ordserv
WHERE ordserv.datpro2 BETWEEN '01/03/2016' AND '30/04/2016'
AND ordserv.statord = 'F'
AND ordserv.codemp = 76
AND 'S' IN (SELECT regserv.executado
FROM regserv
WHERE regserv.codemp = ordserv.codemp
AND regserv.codord = ordserv.codord)
GROUP BY DATEPART(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
) t
OUTER APPLY (SELECT DATEPART(mm, ordserv.datpro2) mes
, DATEPART(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + SUBSTRING(CAST(DATEPART(yy, ordserv.datpro2) AS VARCHAR),
3, 2) mes_ano
, COUNT(*) AS total_ordens_geradas
FROM ordserv
WHERE ordserv.datpro2 BETWEEN '01/03/2016' AND '30/04/2016'
AND ordserv.statord = 'F'
AND ordserv.codemp = 76
--and t.ano = datepart(mm, ordserv.datpro2)
--and t.ano = datepart(yy, ordserv.datpro2)
GROUP BY DATEPART(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
-- I do not think the call to the function is required - but up to you to see if it is or not.
-- and t.mes_ano = engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2)
) total
ORDER BY 1
, 2;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 13, 2016 at 9:47 am
Thank you all for your help. I got the code that Frederico Fonseca. Just fixed the month in the second query and took group by . Thanks
select t.mes
, t.ano
, t.mes_ano
, t.realizadas
, total.total_ordens_geradas
, convert(decimal(5,2), t.realizadas / convert(decimal(5,2), total.total_ordens_geradas) * 100) as porcentagem
from (select datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, sum(case when ordserv.statord='f' then 1 else 0 end) as realizadas
from ordserv
left outer join FILIAL on ORDSERV.CODEMP_7=FILIAL.CODEMP AND ORDSERV.CODFIL=FILIAL.CODFIL
where ordserv.datpro2 between '01/01/2015' and '31/12/2015'
and ordserv.statord='F'
and ordserv.codemp=8
and filial.codfil=25
and 'S' in (select regserv.executado
from regserv
where regserv.codemp = ordserv.codemp
and regserv.codord = ordserv.codord)
group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2)
) t
outer apply (select /*datepart(mm, ordserv.datpro2) mes
, datepart(yy, ordserv.datpro2) ano
, engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2) mes_ano
, */count(*) as total_ordens_geradas
from ordserv
left outer join FILIAL on ORDSERV.CODEMP_7=FILIAL.CODEMP AND ORDSERV.CODFIL=FILIAL.CODFIL
where ordserv.datpro2 between '01/01/2015' and '31/12/2015'
and ordserv.statord='F'
and ordserv.codemp=8
and filial.codfil=25
and t.mes = datepart(mm, ordserv.datpro2)
and t.ano = datepart(yy, ordserv.datpro2)
/* group by datepart(mm, ordserv.datpro2)
, engeman.mesabrev(ordserv.datpro2)
, datepart(yyyy, ordserv.datpro2) */
-- I do not think the call to the function is required - but up to you to see if it is or not.
-- and t.mes_ano = engeman.mesabrev(ordserv.datpro2) + '/' + substring(cast(datepart(yy, ordserv.datpro2) as varchar),3,2)
) total
order by 1,2
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply