I have two Tables CQ_CImpact ( CQID,CImpactTotal) and CQ_BImpact (CQID,BimpactTotal)and two other Tables CIScale ( Scale,MinCI,MaxCI)and BIScale( Scale,MinBI,MaxBI)
The CQ_XXX Tables Contain performance Scores in column BImpacttotal and CImpactTotal(CQID is the primary key for both)and the Scale table contains Scales(performance Rating) and the Minimum Score and Maximum Score.
For every score in CQ_CImpact table there is a scale associated to a range(MinCI,MaxCI) in CIScale table
same for BQ_BImpact Table
Example if CImpactTotal is 88 the the scale is 5. The Scale tables for C and B scores are different.
Scale table looks like this
Scale MinCI MaxCI
1 70 75
2 75 80
3 80 85
4 85 88
5 88 91
6 91 94
7 94 111
I need to write a query where the output is one row: Avg CQ_CScore, Avg CQ_BScore,CIScale,BIScale where CI Scale is the scale associated to overall (Avg CQ_CScore ) and BI Scale is the Scale associated to overall (Avg CQ_BScore)
I have written the below query which does not work because there are some column names in the having clause.
Select M.RepName,round(avg(b.BImpactTotal),2) , round(avg(c.CImpactTotal),2),BS.BIScale,CS.CIScale
from dbo.CQ_BusinessImpact b , dbo.CQ_CustomerImpact c ,dbo.CQ_Monitor M,
dbo.CI_Scale CS,dbo.BI_Scale BS
where b.CQMonitorID = c.CQMonitorID
AND b.CQMonitorID = M.CQMonitorID
--AND M.DateMonitored >= @StartDt AND M.DateMonitored <= @EndDt
--AND round(avg(b.BImpactTotal),2) >= BS.MinBI AND round(avg(b.BImpactTotal),2) < BS.MaxBI
--AND round(avg(c.CImpactTotal),2) >= CS.MinCI AND round(avg(c.CImpactTotal),2) < CS.MaxCI
Group By M.RepName,BS.BIScale,CS.CIScale
Having round(avg(b.BImpactTotal),2) >= BS.MinBI AND round(avg(b.BImpactTotal),2) < BS.MaxBI
AND round(avg(c.CImpactTotal),2) >= CS.MinCI AND round(avg(c.CImpactTotal),2) < CS.MaxCI
The CQ_Monitor table is just another table that I need to get the person's name from...That part seems simple.
Would greatly apprceiate help...