How can I get my LEFT OUTER JOIN to work in SQL Server 2008?

Question

I trying to create a table with this school data in it?

 Year   Month Days Referrals
 2012     1    16     10
 2012     2    20     7
 2012     3    15     11

Year, Month and Count of Days from one table "Calendar" and Referral counts from another table "Referrals".

I'm not able to get the correct Days information since it is only counting the days that have referrals on them. I need all days that are school days whether there was a referral on that day or not.

My Code:

SELECT Datepart(YYYY, DATE_VALUE)                  "YEAR",
       Datepart(MM, DATE_VALUE)                    "MONTH",
       Count(DISTINCT( Datepart(DD, DATE_VALUE) )) "DAYS",
       Count(R.DIS_KEY)                            "REFERRALS"
FROM   CALENDAR C
       LEFT OUTER JOIN REFERRALS R
         ON C.CAL_KEY = R.CAL_KEY
WHERE  C.SC_DAY = 'Y'
       AND C.SC_KEY = @SCHKEY
       AND C.DATE_VALUE BETWEEN @STDATE AND @ENDDATE
GROUP  BY Datepart(MM, C.DATE_VALUE),
          Datepart(YYYY, C.DATE_VALUE)
ORDER  BY 1,
          Datepart(MM, C.DATE_VALUE) 

I keep getting the wrong DAYS count. All else is fine.

I've read all posts I can find on this issue. I don't have any WHERE conditions on the R table except the ON clause of the JOIN. I tried the ISNULL on the GROUP BY fields but that didn't resolve anything.

I'm relatively new to this issue and suspect I may have not fully understood the LEFT OUTER JOIN completely.

I've learned a lot from the blogs but they haven't corrected my issue.

I've never done this before but I have Calendar table:

    CAL_KEY  SC_KEY  DATE_VALUE  SC_DAY 
     10      842     2012-01-15    N -- 2012-01-15 is not a school day. 
     11      842     2012-01-16    Y 
     12      842     2012-01-17    Y  

My Referrals table:

    REF_KEY  SC_KEY  CAL_KEY 
      101     842      11 
      102     842      11 
      103     842      13 -- did you mean for this to be 12? No, there are not referrals on every day. 
                          -- also, where is DIS_KEY mentioned in the query? Sorry REF_KEY = DIS_KEY. EXAMPLE mistake... Referrals are also Disciplines 

Is this enough or do you need to see more?

Answer 1

This is just a guess, since without the data you're expecting even from the small sample data you've shown, or how you got to the results that you posted initially...

;WITH months(m, dc) AS 
(
    -- first, get the number of days for each represented month in the calendar

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATE_VALUE), 0),
     COUNT(DISTINCT DATE_VALUE) -- this can be * unless DATE_VALUE is not unique
     FROM dbo.CALENDAR 
     WHERE SC_DAY = 'Y' 
     AND SC_KEY = @schkey
     AND DATE_VALUE BETWEEN @STDATE AND @ENDDATE
     GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, DATE_VALUE), 0)
),
refs(m, rc) AS
(
    -- now, find all the referrals in each of those months
    -- assume each row in REFERRALS represents a single referral

    SELECT months.m, COUNT(r.CAL_KEY)
     FROM dbo.REFERRALS AS r
     INNER JOIN dbo.CALENDAR AS c
     ON r.CAL_KEY = c.CAL_KEY
     AND r.SC_KEY = c.SC_KEY
     INNER JOIN months
     ON c.DATE_VALUE >= months.m 
     AND c.DATE_VALUE < DATEADD(MONTH, 1, months.m)
     GROUP BY months.m
)
SELECT [Year]    = YEAR(months.m),
       [Month]   = MONTH(months.m),
       [Days]    = months.dc,
       Referrals = COALESCE(refs.rc, 0)
 FROM months 
 LEFT OUTER JOIN refs 
 ON months.m = refs.m
 ORDER BY [Year], [Month];