Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cyclonik
Posting Yak Master
114 Posts |
 Posted - 2001-10-05 : 13:38:19
|
| I have this query which is giving me an error currently (at the spot in bold text). The query runs if i move the derived table in a temp table and do the select count(*) from the temp table. second issue is the left join doesn't return all the appropriate rows from tblUsersT. It works as an Inner Join. i am curious as to why that happens. SELECT COUNT(distinct c.monitor_id) as TotalMonitors, a.lanid,a.Fname,a.Lname, TotalScore = isNull(SUM(b.points),0), TotalAnswered = COUNT(b.monitor_id), Average= isNull(AVG(b.points),0), ExceptionFree = (select count(*) from (SELECT a.lanid, a.monitor_id, min(b.score) as MinScore FROM tblVoice_MonitorsT a JOIN tblVoice_Monitor_Scores_idx b on a.monitor_id = b.monitor_id where monitor_date between @startdate and @enddate and criteria_id <> 13 and criteria_id <> 14 group by a.lanid, a.monitor_id) where lanid = a.lanid and MinScore <> 1), SortCol=CASE 'name' WHEN 'exceptions' THEN STR(SUM(CASE b.Score WHEN 1 THEN 1 ELSE 0 END),10,0) WHEN 'name' THEN a.Lname WHEN 'totalmonitors' then str(COUNT(distinct c.monitor_id),10,0) WHEN 'average' then Str(AVG(b.points),10,0) END FROM tblUsersT a LEFT JOIN tblVoice_MonitorsT c ON a.lanid=c.lanid JOIN tblVoice_monitor_scores_idx b ON c.monitor_id=b.monitor_idWHERE a.managerid = @userid AND (c.monitor_date BETWEEN @startdate and @enddate OR c.monitor_date Is Null) and (c.monitored_by_id = @lanid or c.monitored_by_id is Null) GROUP BY a.Lname,a.Fname,a.lanidOrder by SortCol AscThanks Christian-=:SpasmatiK:=- |
|
|
|
|
|
|
|
Left Join not working