asked on

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

Ashish Patel

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

missatm

ASKER

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.

Jinesh Kamdar

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

ASKER CERTIFIED SOLUTION

Jinesh Kamdar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

missatm

ASKER

That's terrific..Thanks Jinesh...sometimes you overlook simple things and make things complicated ....

Jinesh Kamdar

Glad to be of help :)