missatm
asked on
Function Query help
I have these columns BIScale, count(AvgBIImpactTotal),co unt(TotalA vgScore),W OWScale and I need to come up with a fifth column named BI WeightedScale which is calculated from these Columns (BIScale * Count(Avg(BImpactotal)) + (WOWScale * Count(TotalAvgScore)) / (Count(Avg(BImpactotal)) + Count(TotalAvgScore))
How do I write this formula in a query and get the result in the fifth column?
Thanks
How do I write this formula in a query and get the result in the fifth column?
Thanks
ASKER
It is Count (Avg(BIImpactTotal)). I do not have any fifth column name ..Should it be more like
select BIScale, count(Avg(BIImpactTotal)), count(Tota lAvgScore) ,WOWScale ,
(BIScale * Count(Avg(BImpactotal)) + (WOWScale * Count(TotalAvgScore)) / (Count(Avg(BImpactotal)) + Count(TotalAvgScore)) as WeightedBI
from MyTable
group by ByScale,WowScale
can you tell me which one works?.This query is more like just a part of a big query and I need time to run the whole query...
select BIScale, count(Avg(BIImpactTotal)),
(BIScale * Count(Avg(BImpactotal)) + (WOWScale * Count(TotalAvgScore)) / (Count(Avg(BImpactotal)) + Count(TotalAvgScore)) as WeightedBI
from MyTable
group by ByScale,WowScale
can you tell me which one works?.This query is more like just a part of a big query and I need time to run the whole query...
Try this:
select a.*,
(BIScale * CntAvgBiImpactTotal) + (WOWScale * cntTotalAvgScore) / (CntAvgBiImpactTotal + cntTotalAvgScore) as WeightedBI
from
(select BIScale,
WOWScale,
count(Avg(BIImpactTotal)) as CntAvgBiImpactTotal,
count(TotalAvgScore) as cntTotalAvgScore,
from MyTable
group by ByScale,WowScale) a
ASKER
This is My query for the formula...The last select statement does not work right.Can anyone help?
DROP Table #Temp1
SELECT M.RepEmpID ,
ROUND(AVG(B.BImpactTotal), 2) as BIAvgScore,
(SELECT BIScale FROM TestAarthi.dbo.BI_Scale WHERE AVG(B.BImpactTotal) >= MinBI AND AVG(B.BImpactTotal) < MaxBI)as BIScale,
Count(B.BImpactTotal)as TotalBIMonitors,
ROUND(AVG(C.CImpactTotal), 2) as CIAvgScore,
(SELECT CIScale FROM TestAarthi.dbo.CI_Scale WHERE AVG(C.CImpactTotal) >= MinCI AND AVG(C.CImpactTotal) < MaxCI)as CIScale,
Count(C.CImpactTotal)as TotalCIMonitors,
0 as TotalAvgScore,0 as TotalWOWMonitors,0 as WOWScale
INTO #Temp1
FROM TestAarthi.dbo.CQ_Business Impact B,
TestAarthi.dbo.CQ_Customer Impact C,
TestAarthi.dbo.CQ_Monitor M
WHERE B.CQMonitorID = C.CQMonitorID
AND B.CQMonitorID = M.CQMonitorID
AND M.RepEmpID = '242621'
GROUP BY M.RepEmpID
UNION
Select a.RepID,0,0,0,0,0,0,round( avg(a.Tota lScore + a.BonusScore),2)as TotalAvgScore,(Select Scale from CSWeb.dbo.CSWeb_Scale where avg(a.TotalScore + a.BonusScore) >= MinScore AND avg(a.TotalScore + a.BonusScore) < MaxScore) as WOWScale,count(*) as TotalWOWMonitors
from CSWeb.dbo.CSWeb_DOMCQMGMon itor a
Where a.RepID='242621'
Group By a.RepID
Select a.RepEmpID,Sum(a.BIAvgScor e)as BIAvg,sum(a.BIScale) as BIScale,sum(a.TotalBIMonit ors)as BICount,Sum(a.CIAvgScore)a s CIAvg,Sum(a.TotalCIMonitor s)as CICount,Sum(a.CIScale)as CIScale,Sum(a.TotalAvgScor e)as WOWAvg,Sum(a.WOWScale) as WOWScale,Sum(a.TotalWOWMon itors)as WOWCount
From #Temp1 a
Group By RepEmpID
Select b.*,(b.BICount * b.BIScale)+(b.WOWCount * b.WOWScale)/(b.BICount + b.WOWCount) as WeightedBI,(b.CICount * b.CIScale)+(b.WOWCount * b.WOWScale)/(b.CICount + b.WOWCount) as WeightedCI
From #Temp2 b
DROP Table #Temp1
SELECT M.RepEmpID ,
ROUND(AVG(B.BImpactTotal),
(SELECT BIScale FROM TestAarthi.dbo.BI_Scale WHERE AVG(B.BImpactTotal) >= MinBI AND AVG(B.BImpactTotal) < MaxBI)as BIScale,
Count(B.BImpactTotal)as TotalBIMonitors,
ROUND(AVG(C.CImpactTotal),
(SELECT CIScale FROM TestAarthi.dbo.CI_Scale WHERE AVG(C.CImpactTotal) >= MinCI AND AVG(C.CImpactTotal) < MaxCI)as CIScale,
Count(C.CImpactTotal)as TotalCIMonitors,
0 as TotalAvgScore,0 as TotalWOWMonitors,0 as WOWScale
INTO #Temp1
FROM TestAarthi.dbo.CQ_Business
TestAarthi.dbo.CQ_Customer
TestAarthi.dbo.CQ_Monitor M
WHERE B.CQMonitorID = C.CQMonitorID
AND B.CQMonitorID = M.CQMonitorID
AND M.RepEmpID = '242621'
GROUP BY M.RepEmpID
UNION
Select a.RepID,0,0,0,0,0,0,round(
from CSWeb.dbo.CSWeb_DOMCQMGMon
Where a.RepID='242621'
Group By a.RepID
Select a.RepEmpID,Sum(a.BIAvgScor
From #Temp1 a
Group By RepEmpID
Select b.*,(b.BICount * b.BIScale)+(b.WOWCount * b.WOWScale)/(b.BICount + b.WOWCount) as WeightedBI,(b.CICount * b.CIScale)+(b.WOWCount * b.WOWScale)/(b.CICount + b.WOWCount) as WeightedCI
From #Temp2 b
No where do you have #Temp2 defined in the provided query.
ASKER
OOPS...This is the right one ..Sorry about that!
DROP Table #Temp1
SELECT M.RepEmpID ,
ROUND(AVG(B.BImpactTotal),
(SELECT BIScale FROM TestAarthi.dbo.BI_Scale WHERE AVG(B.BImpactTotal) >= MinBI AND AVG(B.BImpactTotal) < MaxBI)as BIScale,
Count(B.BImpactTotal)as TotalBIMonitors,
ROUND(AVG(C.CImpactTotal),
(SELECT CIScale FROM TestAarthi.dbo.CI_Scale WHERE AVG(C.CImpactTotal) >= MinCI AND AVG(C.CImpactTotal) < MaxCI)as CIScale,
Count(C.CImpactTotal)as TotalCIMonitors,
0 as TotalAvgScore,0 as TotalWOWMonitors,0 as WOWScale
INTO #Temp1
FROM TestAarthi.dbo.CQ_Business
TestAarthi.dbo.CQ_Customer
TestAarthi.dbo.CQ_Monitor M
WHERE B.CQMonitorID = C.CQMonitorID
AND B.CQMonitorID = M.CQMonitorID
AND M.RepEmpID = '242621'
GROUP BY M.RepEmpID
UNION
Select a.RepID,0,0,0,0,0,0,round(
from CSWeb.dbo.CSWeb_DOMCQMGMon
Where a.RepID='242621'
Group By a.RepID
Select a.RepEmpID,Sum(a.BIAvgScor
INTO #Temp2
From #Temp1 a
Group By RepEmpID
Select b.*,((b.BICount * b.BIScale)+(b.WOWCount * b.WOWScale)/(b.BICount + b.WOWCount)) as WeightedBI,((b.CICount * b.CIScale)+(b.WOWCount * b.WOWScale)/(b.CICount + b.WOWCount)) as WeightedCI
From #Temp2 b
Ok... do you care to elaborate on "The last select statement does not work right.Can anyone help?" What is/isn't it doing?
ASKER
My Test values are BIAvg =97.31,BIScale=7,BICount=4 12,CIAvg=9 3.81,CICou nt=412,CIS cale=6,WOW Avg=100,WO WScale=5,W OWCount=7
By calculating the formulate I have in the last select statement it is giving me weightedBI =2884, WeightedCI=2472. It is not giving me the right numbers for both these columns
By calculating the formulate I have in the last select statement it is giving me weightedBI =2884, WeightedCI=2472. It is not giving me the right numbers for both these columns
ASKER
I got this query working
Select b.*,round(((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount),2) as WeightedBI,round(((b.CICou nt * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount),2) as WeightedCI
From #Temp2 b
But...Another BUT...It is truncationg the result to the nearest Integer.. like if the result is 6.01, it is truncating it to 6 ..How can I save 2 decimal points in the result?
Thanks
Select b.*,round(((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount),2) as WeightedBI,round(((b.CICou
From #Temp2 b
But...Another BUT...It is truncationg the result to the nearest Integer.. like if the result is 6.01, it is truncating it to 6 ..How can I save 2 decimal points in the result?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually... I would do it for all items during the creation of the #Temp2 table.
ASKER
why ( 11,2)?
It was just something. That will give you two decimal points.
ASKER
It is giving me 10 to 13 decimal points
ASKER
Thank you for the help
Open in new window