Having Clause Query help

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.RepEmpID='242621'
--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...

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.

Ashish PatelCommented:
Try putting a where clause instead of having with same condition before group by like this.
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.RepEmpID='242621'
--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
 
Where 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

Open in new window

0
missatmAuthor Commented:
That was the first thing I tried..Please see the lines I commented out. It gives me the wrror that where clause cannot have aggregates in it.
0
Jinesh KamdarCommented:
What does ur output look like ? Try this.
SELECT M.RepName,
       ROUND(AVG(B.BImpactTotal),2),
       ROUND(AVG(C.CImpactTotal),2),
       (SELECT BIScale FROM dbo.BI_Scale WHERE AVG(B.BImpactTotal) >= MinBI AND AVG(B.BImpactTotal) < MaxBI)
       (SELECT CIScale FROM dbo.CI_Scale WHERE AVG(C.CImpactTotal) >= MinCI AND AVG(C.CImpactTotal) < MaxCI)
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.RepEmpID = '242621'
GROUP BY M.RepName;

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Jinesh KamdarCommented:
Oops, i left out the FROM clause as is. Re-posting correctly.
SELECT M.RepName,
       ROUND(AVG(B.BImpactTotal),2),
       ROUND(AVG(C.CImpactTotal),2),
       (SELECT BIScale FROM dbo.BI_Scale WHERE AVG(B.BImpactTotal) >= MinBI AND AVG(B.BImpactTotal) < MaxBI)
       (SELECT CIScale FROM dbo.CI_Scale WHERE AVG(C.CImpactTotal) >= MinCI AND AVG(C.CImpactTotal) < MaxCI)
FROM   dbo.CQ_BusinessImpact B,
       dbo.CQ_CustomerImpact C,
       dbo.CQ_Monitor M
WHERE  B.CQMonitorID = C.CQMonitorID 
AND    B.CQMonitorID = M.CQMonitorID 
AND    M.RepEmpID = '242621'
GROUP BY M.RepName;

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
missatmAuthor Commented:
That's terrific..Thanks Jinesh...sometimes you overlook simple things and make things complicated ....
0
Jinesh KamdarCommented:
Glad to be of 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.