Inaccurate counts/joining in multiple stats tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Inaccurate counts/joining in multiple stats tables

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-05-14 : 14:18:58

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 time

Code 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)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-14 : 16:29:39

You can use the apply operator like shown below.

SELECT
	p.user_id,
	p.name,
	COALESCE(a.StatA,0) AS StatA,
	COALESCE(b.StatB,0) AS StatB
FROM
	#people p
	OUTER APPLY
	(
		SELECT SUM(CASE WHEN compliant= 1 THEN 1 ELSE 0 END) AS StatA
		FROM #complianceA a
		WHERE a.user_id = p.user_id
	) a
	OUTER APPLY
	(
		SELECT SUM(CASE WHEN compliant= 1 THEN 1 ELSE 0 END) AS StatB
		FROM #complianceB b
		WHERE b.user_id = p.user_id
	) b

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2014-05-14 : 17:02:19

This seems to solve the issue nicely. I was looking into using PIVOT to accomplish this, but I couldn't find a way to change the aggregate function for each column. This makes it trivial to format the data however I need.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources