SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Inaccurate counts/joining in multiple stats tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jaypoc
Starting Member

USA
46 Posts

Posted - 05/14/2014 :  14:18:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 05/14/2014 :  16:29:39  Show Profile  Reply with Quote
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	
Go to Top of Page

Jaypoc
Starting Member

USA
46 Posts

Posted - 05/14/2014 :  17:02:19  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000