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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jinesh KamdarConnect With a Mentor Commented:
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
All Courses

From novice to tech pro — start learning today.