August 30, 2011 at 11:54 am
I have this small script, and now all I am needing is the percent from num_participants/num_Meeting_Goal but the value does is not correct.
I need some help.
program_key program_namenum_participantsNum_Meeting_Goal Program Participation Level
692011 G3X 842443100.00
To get my percent I want (443/842) * 100 but in my results is gives me 100%.
My script:
declare @theYear varchar(50)
set @theYear = '2011'
;With MyCTE(id, [Program_Name], [year], num_participants)
AS
(
select /*pp.program_key,*/
mp.ID as Program_Key,
--pp.particip_key,
mp.[Program_Name],
mp.[year],
count(pp.particip_key) As '#Participants'
from mkt_programs mp
inner join program_particips pp on pp.program_key = mp.id
inner join participants p on p.id = pp.particip_key
where
p.participant_name is not null
-- and mp.program_type = 'Marketing'
and mp.[year] = @theYear
--and p.participant_name like '311%'
--and pp.program_key = 50
Group by pp.program_key, mp.[Program_Name], mp.id, mp.[year]
)
select
gs.program_key,
mc.[program_name],
mc.num_participants,
ISNULL(sum(isnull(gs.units,0))/NULLIF(sum(gs.sales), 0), 0)*100 as [Sales_Percentage]
,Num_Meeting_Goal=
Case
when ISNULL(sum(isnull(gs.units,0))/NULLIF(sum(gs.sales), 0), 0)*100 <= 1
then 1
end
into #ttemp
from goal_account_summary gs
left join MyCTE mc on mc.id = gs.program_key
where
gs.dateyear = @theYear
and gs.goal_key <>75
group by gs.program_key,gs.particip_key, mc.[program_name],mc.num_participants
--, datemonth
order by mc.[program_name],gs.program_key
--select * from #ttemp
--where program_key = 117
--1172011 COOPER MEDALLION PGM2315
select
program_key,
[program_name],
num_participants,
ISNULL(count(Num_Meeting_Goal),0) as Num_Meeting_Goal
--,Cast(num_participants/count(Num_Meeting_Goal) * 100 as decimal(18,2) ) As 'Program Participation Level'
into #ttemp2
from #ttemp
--where program_key = 69
group by program_key, [program_name],
num_participants
order by program_key asc
select
program_key,
[program_name],
num_participants,
Num_Meeting_Goal
,Cast(SUM(num_participants/Num_Meeting_Goal) * 100 as decimal(18,2) ) As 'Program Participation Level'
from #ttemp2
group by program_key, [program_name],
num_participants, num_meeting_goal
order by program_key asc
drop table #ttemp
drop table #ttemp2
If there is a better way, of doing this please let me know and help me out.
Thanks,
Keith.
August 30, 2011 at 12:27 pm
It's doing integer division, which rounds. Convert one/both numbers to decimal or float datatypes, in the query, and you should get what you need.
Try this to see the difference:
SELECT 7/10, CAST(7 AS FLOAT)/10;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 12:35 pm
That was it.
,SUM(Cast(Num_Meeting_Goal AS Decimal(18,2))/num_participants) * 100 As 'Program Participation Level2'
I was doing the division first then casting it as decimal, which did not work.
Thank you,
Keith
August 30, 2011 at 1:04 pm
You're welcome.
That one catches everyone sooner or later.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply