Items getting double-counted in SQL Server, dependent counting logic not working right

I am counting the number of RFIs (requests for info) from various agencies. Some of these agencies are also part of a task force (committee). Currently this SQL combines the agencies and task forces into one list and counts the RFIs for each. The problem is, if the RFI belongs to a task force (which is also assigned to an agency), I only want it to count for the task force and not for the agency. However, if the agency does not have a task force assigned to the RFI, I want it to still count for the agency. The RFIs are linked to various agencies through a _LinkEnd table, but that logic works just fine. Here is the logic thus far:

SELECT t.Submitting_Agency, COUNT(DISTINCT t.Count) AS RFICount 
FROM (
    SELECT RFI_.Submitting_Agency, RFI_.Unique_ID, _LinkEnd.EntityType_ID1, _LinkEnd.Link_ID as Count
        FROM RFI_
            JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
        WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630'
    UNION ALL 
    SELECT RFI_.Task_Force__Initiative AS Submitting_Agency, RFI_.Unique_ID, _LinkEnd.EntityType_ID1, _LinkEnd.Link_ID as Count
        FROM RFI_
        JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
        WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630' AND RFI_.Task_Force__Initiative IS NOT NULL) t
GROUP BY t.Submitting_Agency

How can I get it to only count an RFI one time, even though the two fields are combined? For instance, here are sample records from the RFI_ table:

---------------------------------------------------------------------------
| Unique_ID | Submitting_Agency | Task_Force__Initiative | Date_Submitted |
---------------------------------------------------------------------------
| 1         | Social Service    | Flood Relief TF        | 2011-05-08     |
---------------------------------------------------------------------------
| 2         | Faith-Based Init. | Homeless Shelter Min.  | 2011-06-08     |
---------------------------------------------------------------------------
| 3         | Psychology Group  |                        | 2011-05-04     |
---------------------------------------------------------------------------
| 4         | Attorneys at Law  |                        | 2011-05-05     |
---------------------------------------------------------------------------
| 5         | Social Service    |                        | 2011-05-10     |
---------------------------------------------------开发者_JAVA技巧------------------------

So assuming only one link existed to one RFI for each of these, the count should be as follows:

Social Service        1
Faith-Based Unit.     0
Psychology Group      1
Attorneys at Law      1
Flood Relief TF       1
Homeless Shelter Min. 1

Note that if both an agency and a task force are in one record, then the task force gets the count, not the agency. But it is possible for the agency to have a record without a task force, in which case the agency gets the count. How could I get this to work in this fashion so that RFIs are not double-counted? As it stands both the agency and the task force get counted, which I do not want to happen. The task force always gets the count, unless that field is blank, then the agency gets it.

I guess a simple COLESCE() would do the trick?

SELECT COLAESCE(Task_Force__Initiative, Submitting_Agency), COUNT(DISTINCT _LinkEnd.Link_ID) AS RFICount 
  FROM RFI_
  JOIN _LinkEnd ON RFI_.Unique_ID=_LinkEnd.Entity_ID1
 WHERE _LinkEnd.Link_ID LIKE 'CAS%' AND RFI_.Date_Submitted BETWEEN '20110430' AND '20110630'
 GROUP BY COLAESCE(Task_Force__Initiative, Submitting_Agency);

Rather than:

SELECT t.Submitting_Agency ...

Try

SELECT
    CASE t.[Task_Force__Initiative]
        WHEN NULL THEN -- Or whatever value constitutes "empty" 
            t.[Submitting_Agency]
        ELSE
            t.[Task_Force__Initiative] 
    END ...

and then GROUP BY the same.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

The result will be that your count will aggregate from the proper specified grouping point, rather than from the single agency column.

EDIT: From your example it looks like you don't use NULL for the empty field but maybe a blank string? In that case you'll want to replace the NULL in the CASE above with the proper "blank" value. If it is NULL then you can COALESCE as suggested in the other answer.

EDIT: Based on what I think your schema is... and your WHERE criteria

SELECT 
    COALESCE(RFI_.[Task_Force__Initiative], RFI_.[Submitting_Agency]),
    COUNT(*)
FROM 
    RFI_ 
    JOIN _LinkEnd 
        ON RFI_.[Unique_ID]=_LinkEnd.[Entity_ID1]
WHERE 
    _LinkEnd.[Link_ID] LIKE 'CAS%' 
    AND RFI_.[Date_Submitted] BETWEEN '20110430' AND '20110630'
GROUP BY
    COALESCE(RFI_.[Task_Force__Initiative], RFI_.[Submitting_Agency])