Q: Multiple joins to single table in SQL Server query throwing off counts I am writing a stored procedure in SQL Server Management Studio 2005 to return a list of states and policy counts for two different time periods, month to date and year to date. I have created a couple of views to gather the required data and a stored procedure for use in a Reporting Services report. 
Below is my stored procedure:
SELECT DISTINCT
    S.[State],
    COUNT(HP_MTD.PolicyID) AS PolicyCount_MTD,
    COUNT(HP_YTD.PolicyID) AS PolicyCount_YTD
FROM tblStates S
    LEFT OUTER JOIN vwHospitalPolicies HP_MTD ON S.[State] = HP.[State]
        AND HP.CreatedDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
    LEFT OUTER JOIN vwHospitalPolicies HP_YTD ON S.[State] = HP.[State]
        AND HP.CreatedDate BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE()
GROUP BY S.[State]
ORDER BY S.[State] ASC

The problem I am running into is my counts are bloating when a second LEFT OUTER JOIN is added, even the COUNT() that isn't referencing the second join. I need a left join since not all states will have policies for the given period, but they should still appear on the report.
Any suggestions would be greatly appreciated!

A: It sounds like you need:
COUNT(DISTINCT HP_MTD.PolicyID) AS PolicyCount_MTD,
COUNT(DISTINCT HP_YTD.PolicyID) AS PolicyCount_YTD

instead of:
COUNT(HP_MTD.PolicyID) AS PolicyCount_MTD,
COUNT(HP_YTD.PolicyID) AS PolicyCount_YTD

Your original query is including the number of matching rows in the second join. Adding a DISTINCT clause inside the COUNT limits it to unique occurrences of the PolicyID.

A: I prefer CTEs for this sort of work - they're basically a sort of inline view.  
Your actual problem is that some policies are being counted twice - once for the month-to-date, and once for the year-to-date - in both count columns.  So, if you have source tables like this:
year-to-date
state   policy
==================
1       1
1       2
1       3
2       4

month-to-date
state   policy
==================
1       1
1       2

The result table for the JOINs (before COUNT is assesed) looks like this:
temp
state  monthPolicy  yearPolicy
================================
1      1            1
1      1            2
1      1            3
1      2            1
1      2            2
1      2            3
2      -            4

Clearly not what you want.  Often, the solution is to use a CTE or other table reference to present summed-up records for the final join.  Something like this:  
WITH PoliciesMonthToDate (state, count) as (
                          SELECT state, COUNT(*)
                          FROM vwHospitalPolicies
                          WHERE createdDate >= DATEADD(MONTH, -1, GETDATE())
                          AND createdDate < DATEADD(DAY, 1, GETDATE())
                          GROUP BY state),
     PoliciesYearToDate (state, count) as (
                         SELECT state, COUNT(*)
                         FROM vwHospitalPolicies
                         WHERE createdDate >= DATEADD(YEAR, -1, GETDATE())
                         AND createdDate < DATEADD(DAY, 1, GETDATE())
                         GROUP BY state)
SELECT a.state, COALESCE(b.count, 0) as policy_count_mtd, 
                COALESCE(c.count, 0) as policy_count_ytd
FROM tblStates a
LEFT JOIN PoliciesMonthToDate b
ON b.state = a.state
LEFT JOIN PoliciesYearToDate c
ON c.state = a.state
ORDER BY a.state

(minor nitpick - don't use prefixes for tables and views.  It's noise, and if for some reason you switch between them, it would require a code change.  That, or the name would then be misleading)
