SQL Join Not Returning What I Expect

Question

I have a temp table I am creating a query off of in the following format. That contains a record for every CustomerID, Year, and Month for several years.

#T

Customer | CustomerID | Year | Month

ex.

Foo | 12345 | 2008 | 12  
Foo | 12345 | 2008 | 11  
Bar | 11224 | 2007 | 7

When I join this temp table to another table of the following format I get many more results than I am expecting.

Event

EventID | CustomerID | DateOpened

ex.

1100 | 12345 | '2008-12-11 10:15:43'
1100 | 12345 | '2008-12-11 11:25:17'

I am trying to get a result set of the count of events along with the Customer, Year, and Month like this.

SELECT COUNT(EventID), Customer, Year, Month
FROM [Event] 
JOIN #T ON [Event].CustomerID = #T.CustomerID
WHERE [Event].DateOpened BETWEEN '2008-12-01' AND '2008-12-31'
GROUP BY Customer, Year, Month
ORDER BY Year, Month

I am getting a record for every Year and Month instead of only for December 2008.

Answer 1

The problem is that there are two rows in #T with CustomerID = 12345. Each of those rows joins with each of the rows in Event. If you only want the CustomerID in December, then you need to filter #T too:

SELECT COUNT(EventID), Customer, Year, Month
    FROM [Event] 
    JOIN #T ON [Event].CustomerID = #T.CustomerID
    WHERE [Event].DateOpened BETWEEN '2008-12-01' AND '2008-12-31'
      AND #T.Year = 2008
      AND #T.Month = 12
    GROUP BY Customer, Year, Month
    ORDER BY Year, Month

If you have some other expectation, you'd better clarify your question.

Answer 2

You're specifying the date on the event table but not on the join -- so it's joining all records from the temp table with a matching customerid.

Try this:

SELECT COUNT(e.EventID), T.Customer, T.Year, T.Month
FROM [Event] e
INNER JOIN #T T ON (
  T.CustomerID = e.CustomerID and 
  T.Year = year(e.DateOpened) and 
  T.Month = month(e.DateOpened) 
  ) 
WHERE T.Year = 2008 
  and T.Month = 12 
GROUP BY T.Customer, T.Year, T.Month
ORDER BY T.Year, T.Month

Answer 3

Perhaps what you mean is:

SELECT COUNT(EventID)
    ,Customer
    ,Year
    ,Month
FROM [Event] 
INNER JOIN #T
    ON [Event].CustomerID = #T.CustomerID
    AND YEAR([Event].DateOpened) = #T.YEAR
    AND MONTH([Event].DateOpened) = #T.MONTH
WHERE [Event].DateOpened >= '2008-12-01'
    AND [Event].DateOpened < '2009-01-01'
GROUP BY Customer
    ,Year
    ,Month
ORDER BY Year
    ,Month

Note, I've fixed another latent bug in your code: your BETWEEN is going to exclude datetimes like '2008-12-31 10:15:43' You can use this or similar technique.

Answer 4

It seams that #T has redundant information if you only want the customer name.

You can solve it with a subquery

SELECT COUNT(EventID), (select TOP 1 #T.Customer from #T Where #T.CustomerID = [Event].CustomerID ), Year, Month
FROM [Event] 
WHERE [Event].DateOpened BETWEEN '2008-12-01' AND '2008-12-31'
GROUP BY CustomerID, Year, Month
ORDER BY Year, Month