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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.