We help IT Professionals succeed at work.

Function Query help

Medium Priority
286 Views
Last Modified: 2010-03-20
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

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

Open in new window

Author

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...
 
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

Open in new window

Author

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

FROM   TestAarthi.dbo.CQ_BusinessImpact B,

       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


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

Author

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

FROM   TestAarthi.dbo.CQ_BusinessImpact B,

       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
Ok... do you care to elaborate on "The last select statement does not work right.Can anyone help?"  What is/isn't it doing?

Author

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

Author

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

Open in new window

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

Author

Commented:
why ( 11,2)?
It was just something.  That will give you two decimal points.

Author

Commented:
It is giving me 10 to 13 decimal points

Author

Commented:
Thank you for the help

Explore More ContentExplore courses, solutions, and other research materials related to this topic.