We help IT Professionals succeed at work.

# Function Query help

on
Medium Priority
286 Views
I have these columns BIScale, count(AvgBIImpactTotal),count(TotalAvgScore),WOWScale 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
Comment
Watch Question

## View Solution Only

Commented:
For starters, you have count(avgBiImpactTotal) in the field list... and count(avg(BiImpactTotal)) in the formula for BI.  Whch is it?
``````select BIScale, count(AvgBIImpactTotal),count(TotalAvgScore),WOWScale ,
BI =(BIScale * Count(Avg(BImpactotal)) + (WOWScale * Count(TotalAvgScore)) / (Count(Avg(BImpactotal)) + Count(TotalAvgScore))

from MyTable
group by ByScale,WowScale
``````

Commented:
It is Count (Avg(BIImpactTotal)). I do not have any  fifth column name ..Should it be more like

select BIScale, count(Avg(BIImpactTotal)),count(TotalAvgScore),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...

Commented:
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
``````

Commented:
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

TestAarthi.dbo.CQ_CustomerImpact 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.TotalScore + 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_DOMCQMGMonitor a

Where a.RepID='242621'

Group By a.RepID

Select a.RepEmpID,Sum(a.BIAvgScore)as BIAvg,sum(a.BIScale) as BIScale,sum(a.TotalBIMonitors)as BICount,Sum(a.CIAvgScore)as CIAvg,Sum(a.TotalCIMonitors)as CICount,Sum(a.CIScale)as CIScale,Sum(a.TotalAvgScore)as WOWAvg,Sum(a.WOWScale) as WOWScale,Sum(a.TotalWOWMonitors)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

Commented:
No where do you have #Temp2 defined in the provided query.

Commented:

OOPS...This is the right one ..Sorry about that!

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

TestAarthi.dbo.CQ_CustomerImpact 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.TotalScore + 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_DOMCQMGMonitor a

Where a.RepID='242621'

Group By a.RepID

Select a.RepEmpID,Sum(a.BIAvgScore)as BIAvg,sum(a.BIScale) as BIScale,sum(a.TotalBIMonitors)as BICount,Sum(a.CIAvgScore)as CIAvg,Sum(a.TotalCIMonitors)as CICount,Sum(a.CIScale)as CIScale,Sum(a.TotalAvgScore)as WOWAvg,Sum(a.WOWScale) as WOWScale,Sum(a.TotalWOWMonitors)as WOWCount

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

Commented:
Ok... do you care to elaborate on "The last select statement does not work right.Can anyone help?"  What is/isn't it doing?

Commented:
My Test values are BIAvg =97.31,BIScale=7,BICount=412,CIAvg=93.81,CICount=412,CIScale=6,WOWAvg=100,WOWScale=5,WOWCount=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

Commented:
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.CICount * 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
Commented:
If you divide integer values you will always have whole number results.  If you cast one of the values as a decimal with a precision of 2, they will all be cast to that and your precision will be 2 for the entire statement.

Try this.  I just added one cast as numeric(11,2)
``````Select b.*,round(((cast(b.BICount as numeric(11,2) * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount),2) as WeightedBI,round(((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount),2) as WeightedCI

From #Temp2 b
``````

Commented:
Actually... I would do it for all items during the creation of the #Temp2 table.

Commented:
why ( 11,2)?

Commented:
It was just something.  That will give you two decimal points.

Commented:
It is giving me 10 to 13 decimal points

Commented:
Thank you for the help