Link to home
Start Free TrialLog in
Avatar of missatm
missatm

asked on

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
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

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

Avatar of missatm
missatm

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

Avatar of missatm

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_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.
Avatar of missatm

ASKER


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?
Avatar of missatm

ASKER

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
Avatar of missatm

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.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
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually... I would do it for all items during the creation of the #Temp2 table.
Avatar of missatm

ASKER

why ( 11,2)?
It was just something.  That will give you two decimal points.
Avatar of missatm

ASKER

It is giving me 10 to 13 decimal points
Avatar of missatm

ASKER

Thank you for the help