% of data completeness

• Thejudderman

  Valued Member

  Points: 71

  More actions

  December 6, 2011 at 8:53 am

  #261070

  Hello Forum,

  Another "newby" question I'm afraid....

  I am trying to figure out if it is possible to show, as a percentage, the amount of fields that have data in them for a given range of records. Also, I need to be able to provide a dynamic parameter where the parameter get's it's value from a query and not from the user ? Is this possible ??

  Here is a small example of what I am trying to do..... For example....I have 4 records with the following information....

  field01field02field03field04field05field06field07field08field09field10

  1abc XXX XXX XXX XXX XXX

  2def XXX XXX XXX XXX

  3abc XXX XXX XXX XXX XXX XXX

  4def XXX XXXXXX XXX

  Rows 1 and 3 are related via field01, rows 2 and 4 are also related via field01. Can I group those two rows together and work out the percentage of fields that have data ??

  When the report runs it needs to get, (hopefully) via a parameter, all the "groups" of records, then figure out the completeness.

  Any pointers/advice would be greatly appreciated.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 6, 2011 at 12:56 pm

  #1417631

  Do not know if this will truly assist you, it is long and cumbersome, but it might get started.

  CREATE TABLE #T(field01 int, field02 VARCHAR(3), field03 VARCHAR(3), field04 VARCHAR(3)

  , field05 VARCHAR(3), field06 VARCHAR(3), field07 VARCHAR(3), field08 VARCHAR(3), field09 VARCHAR(3)

  , field10 VARCHAR(3))

  INSERT INTO #T

  SELECT 1, 'abc', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX',NULL,NULL,NULL UNION ALL

  SELECT 2, 'def', 'XXX', 'XXX', 'XXX', 'XXX', NULL,NULL,NULL,NULL UNION ALL

  SELECT 3, 'abc', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX','XXX',NULL,NULL UNION ALL

  SELECT 4, 'def', 'XXX', 'XXX', 'XXX', 'XXX', NULL,NULL, NULL,NULL

  SELECT Field02 AS 'Group',COUNT(field02)AS '# in field02',COUNT(field03)'# in field03' ,

  COUNT(field04),COUNT(field05)AS '# in field05',

  COUNT(field06),COUNT(field07) AS '# in field07',COUNT(field08)AS '# in field08' ,

  COUNT(field09) AS '# in field09',

  COUNT(field10) FROM #T GROUP BY field02

  Results: -- not all titles listed, just too much to post

  Group #in field 02 #in field 08

  abc222222100

  def222220000

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]