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.
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?