Function Query help

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
missatmAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor 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

Open in new window

0
 
BrandonGalderisiCommented:
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

0
 
missatmAuthor 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...
 
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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

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


0
 
BrandonGalderisiCommented:
No where do you have #Temp2 defined in the provided query.
0
 
missatmAuthor 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
0
 
BrandonGalderisiCommented:
Ok... do you care to elaborate on "The last select statement does not work right.Can anyone help?"  What is/isn't it doing?
0
 
missatmAuthor 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
0
 
missatmAuthor 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
0
 
BrandonGalderisiCommented:
Actually... I would do it for all items during the creation of the #Temp2 table.
0
 
missatmAuthor Commented:
why ( 11,2)?
0
 
BrandonGalderisiCommented:
It was just something.  That will give you two decimal points.
0
 
missatmAuthor Commented:
It is giving me 10 to 13 decimal points
0
 
missatmAuthor Commented:
Thank you for the help
0
All Courses

From novice to tech pro — start learning today.