Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you show the source data please? –  Aaron Bertrand Jun 6 '12 at 22:49
    
Not sure what you mean since the data is 365 lines in one table and 1000's of lines in the other but the C table has the date_value of every day of the year with the School Day indicator "SC_DAY" field in it. I should have the # of school days of the month as stated in my example. The referrals table R has all the referrals from the year with a key field linked to the C table CAL_KEY. If there are 40 referrals in the month of 3 and 15 school days I want to see that data but I'm only getting the school days that have referrals... Does that help? –  ajh Jun 6 '12 at 23:28
4  
How about enough source data to show some valid results that you expect from that data (I wasn't asking for a database backup). We have no idea how you got your three rows of summary data. We could make up some data but then we could just be chasing our tails wondering if the results are wrong because we weren't working from the same data or because we've misunderstood your word problem requirement. –  Aaron Bertrand Jun 6 '12 at 23:31
1  
You are saying that the COUNT(DISTINCT(DATEPART(...))) should return a number other than 16 for January 2012? How many SC_DAY = 'Y' in January 2012? Note that any referrals on non-school days appear to be left out. –  Cade Roux Jun 7 '12 at 0:49

1 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];
share|improve this answer
    
@Cade No I want it to be 16, that is the number of school days in January! I won't have referrals on non-school days since no one will be working at the school to give a referral. If there is an occurance on non-school days the referral(s) will be handled on Monday. –  ajh Jun 7 '12 at 14:51
1  
I read this question yesterday and came across Kendra Little's post about dataset gaps today. You may want to take a look at it. It's similar to Aaron's solution: littlekendra.com/2010/12/23/mindthegap –  MarlonRibunal Jun 7 '12 at 18:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.