including 0 with count

• Paul Morris-1011726

  SSCommitted

  Points: 1879

  More actions

  December 7, 2010 at 7:56 am

  #242169

  I am using BIDS with SQL 2008.

  I need to include the Initials of users even if there is a 0 count. I looked around and see how to do it if something exists to count but in this case there isn't... Not sure how to handle this.

  Test Data and Query:

  --=============================================================

  -- temp table for test data

  --========================================================

  IF OBJECT_ID(N'#cases') IS NOT NULL

  DROP TABLE #cases

  GO

  CREATE TABLE #cases

  (

  case_sk int NOT NULL,

  create_user_id varchar(4),

  create_date datetime

  )

  INSERT INTO #cases (case_sk, create_user_id, create_date)

  SELECT 11111, 'AAA', '2010-11-26' UNION ALL

  SELECT 22222, 'BBB', '2010-11-06' UNION ALL

  SELECT 33333, 'AAA', '2010-11-28' UNION ALL

  SELECT 44444, 'CCC', '2010-11-12'

  -- SELECT * FROM #cases

  --========================================================

  -- Query

  --========================================================

  SELECT create_user_id AS Initials

  FROM #cases AS c

  GROUP BY create_user_id, create_date, case_sk

  HAVING (create_user_id IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE'))

  AND (create_date BETWEEN '2010-11-01' AND '2010-11-30')

  ORDER BY Initials

  DROP TABLE #cases

  Sample Results:

  Initials Count

  AAA 2

  BBB 1

  CCC 1

  DDD 0

  EEE 0

  I am handling the Totals in the design of report... not sure if it is better there or in query...

  Any help would be appreciated...thanks!

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  December 7, 2010 at 8:07 am

  #1259549

  you will need to insert the required userId's into a table and then left join from that back to your #cases table

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Paul Morris-1011726

  SSCommitted

  Points: 1879

  More actions

  December 7, 2010 at 9:36 am

  #1259624

  Thanks Dave... Had to play a bit with it. On the report created a seperate dataset. I had trouble cause it kept using the date range and excluding all the initials with 0 count... so basically took the query, inserted the intials into temp table, created and inserted the entire list into another temp table LEFT JOIN them then used IS NULL to find the ones with 0... I used this to work through it:

  http://www.sqlservercentral.com/Forums/Topic806951-145-1.aspx

  Thanks again for pointing me in the right direction!