I could use some help with the following query: I have the following 3 tables in SQL Server 2008 R2: #people: #complianceA: #complianceB: id | user_id | name user_id | date | compliant user_id | date | compliant -------------------- --------|----------|---------- --------|----------|---------- 1 | 12345 | Jason 12345 | 2014-1-1 | 1 12345 | 2014-2-1 | 1 2 | 12346 | Mike 12345 | 2014-2-1 | 0 12345 | 2014-3-1 | 1 12345 | 2014-2-1 | 1 12345 | 2014-3-1 | 0
I want to build a query that can take a list of id's and for each one, display the user_id, name and how many of each statistic are compliant (determined by a 1). I tried the following query: SELECT user_id ,name ,COUNT(CASE WHEN a.compliant = 1 THEN 1 ELSE NULL END) StatA -- count of StatA's with a 1 value ,COUNT(CASE WHEN b.compliant = 1 THEN 1 ELSE NULL END) StatB -- count of StatB's with a 1 value FROM #people p LEFT JOIN #complianceA a ON p.user_id = a.user_id AND a.date > '2014-01-01' AND a.date < '2014-04-01' LEFT JOIN #complianceB b ON p.user_id = b.user_id AND a.date > '2014-01-01' AND a.date < '2014-04-01' WHERE id in (1,2) GROUP BY id, name
(Note the Date range and list of id's in the where clause were provided by the user via the UI)I am getting: user_id | name | StatA | StatB ------------------------------- 12345 | Jason | 3 | 4 12346 | Mike | 0 | 0
What I want to get is: user_id | name | StatA | StatB ------------------------------- 12345 | Jason | 2 | 2 12346 | Mike | 0 | 0
I don't know that it matters, but the final project will pull from about 10 statistics tables like this for 5-15 user id's at a timeCode to create/populate temp tables above: CREATE TABLE #people ( id int, user_id int, name varchar(15) ) CREATE TABLE #complianceA ( user_id int, date datetime, compliant bit ) CREATE TABLE #complianceB ( user_id int, date datetime, compliant bit ) INSERT INTO #people (id, user_id, name) VALUES (1,12345,'Jason') INSERT INTO #people (id, user_id, name) VALUES (2,12346,'Mike') INSERT INTO #complianceA (user_id, date, compliant) VALUES (12345,'2014-01-01',1) INSERT INTO #complianceA (user_id, date, compliant) VALUES (12345,'2014-02-01',0) INSERT INTO #complianceA (user_id, date, compliant) VALUES (12345,'2014-02-01',1) INSERT INTO #complianceB (user_id, date, compliant) VALUES (12345,'2014-02-01',1) INSERT INTO #complianceB (user_id, date, compliant) VALUES (12345,'2014-03-01',1) INSERT INTO #complianceB (user_id, date, compliant) VALUES (12345,'2014-03-01',0)