In MSSQL, I have a table with a bunch of auditors. Long story short, i want to show ALL my auditors listed out. The problem is when i use where clause, it stops showing all the auditors and only shows the ones that meet the criteria of the WHERE clause. Is there a way to show all of the auditors in the table regardless of the where clause. So if they don't meet the criteria just have the SUM and COUNT = 0?
Here's my sql ( i cut it short so it's an easier read...):
, COUNT(DISTINCT case when claims.dateon >='20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30
, SUM(case when claims.dateon >= '20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30
INNER JOIN claims ON claims.auditorID = auditors.auditorID
INNER JOIN entries ON claims.rID = entries.rid
WHERE claims.status = 'closed'
AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
ORDER BY auditors.fName