how to calculate percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to calculate percentage

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2010-09-27 : 15:08:29

How would i calculate a percentage using these two queries, I tried this syntax but it didnt work, can you someone tell me what i am doing wrong?

select cast (y.noplx as numeric)/cast(x.plx as numeric) * 100
from
(

select distinct programappid plx, jo.idnum, svp.username, svp.actualdate, jo.dotcode, oc.sochigh occdesc
from serviceplan svp join joborders jo
on svp.joidnum=jo.idnum left outer join ospr_workarea.dbo.onet2010 oc
on jo.onetcode=oc.soc
where 1=1
and svp.col_lwia=@rwb
and jo.office IN ('4122','4123')
and jo.dotcode IN ('29111100','29206100')
and convert(smalldatetime, convert(varchar(10),svp.actualdate, 101))
BETWEEN @bgDte and @enDte
and svp.servicecode between '750' and '881'
and svp.programappid is not null
group by jo.idnum, svp.username, svp.actualdate, jo.dotcode, oc.sochigh
)x,

(
select distinct programappid noplx, jo.idnum, svp.username, svp.actualdate, jo.dotcode, oc.sochigh occdesc
from serviceplan svp join joborders jo
on svp.joidnum=jo.idnum left outer join ospr_workarea.dbo.onet2010 oc
on jo.onetcode=oc.soc
where 1=1
and svp.col_lwia=@rwb
and jo.office IN ('4122','4123')
and jo.dotcode IN ('29111100','29206100')
and svp.servicecode NOT between '750' and '881'
and convert(smalldatetime, convert(varchar(10),svp.actualdate, 101))
BETWEEN @bgDte and @enDte
and svp.programappid is not null
group by jo.idnum, svp.username, svp.actualdate, jo.dotcode, oc.sochigh
)y

Roger DeFour

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-27 : 15:31:11

Are you trying to count something? Can you explain what you're trying to do?

Trininole
Yak Posting Veteran

83 Posts

Posted - 2010-09-27 : 16:00:43

Yes, i am trying to calculate the placement rate(percentage) amount of nurses positions placed which is query "X" over the amount of nurse positions not filled which is "Y" and calculate a percentage of that.

Roger DeFour

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-27 : 16:12:39

See if this works, if it does, I'll explain it in more detail. If it doesn't work I'll try again:

SELECT SUM(CASE WHEN svp.servicecode NOT BETWEEN '750' AND '881' THEN 1 ELSE 0 END)/1./
SUM(CASE WHEN svp.servicecode BETWEEN '750' AND '881' THEN 1 ELSE 0 END)*100e
FROM serviceplan svp 
JOIN joborders jo ON svp.joidnum=jo.idnum 
LEFT OUTER JOIN ospr_workarea.dbo.onet2010 oc ON jo.onetcode=oc.soc
WHERE svp.col_lwia=@rwb
AND jo.office IN ('4122','4123')
AND jo.dotcode IN ('29111100','29206100') 
AND svp.actualdate BETWEEN @bgDte AND DATEADD(ms, 86399997, @enDte)
AND svp.programappid IS NOT NULL

I'm not sure what the columns mean so can't guarantee this is the correct logic. You'll need to post the DDL for the tables involved if the results aren't accurate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources