COUNT not working

• alorenzini

  SSCarpal Tunnel

  Points: 4620

  More actions

  April 10, 2008 at 9:12 am

  #181289

  From the following partial data set:

  COnsultantID Active DeactivationDate StatusID

  ------------- ------ ----------------------- --------------------------------------------------

  0067960 0 2007-09-04 00:00:00.000 72HOURRESIGNATION

  0067960 0 2007-09-04 00:00:00.000 72HOURRESIGNATION

  0067643 0 2007-09-04 00:00:00.000 NULL

  0067643 0 2007-09-04 00:00:00.000 NULL

  The following query correctly returns four rows of data:

  Select COnsultantID, Active, DeactivationDate,StatusID from Consultant

  Where DeactivationDate = '2007-09-04'

  The following query correctly counts two distinct rows:

  Select Count (Distinct ConsultantID) from Consultant

  Where DeactivationDate = '2007-09-04'

  The following query correctly counts one distinct row:

  Select Count (Distinct ConsultantID) from Consultant

  Where DeactivationDate = '2007-09-04'

  And StatusID = '72HOURRESIGNATION'

  Why does the following query return zero rows? (I expected one row here, too, since the logic should be just the opposite of the previous query.)

  Select Count (Distinct ConsultantID) from Consultant

  Where DeactivationDate = '2007-09-04'

  And StatusID <> '72HOURRESIGNATION'

  Art

  Thanks,
  Art
  Database Analyst
  Tastefully Simple, Inc.

  alorenzini@tastefullysimple.com
  " Some days, it's not even worth chewing through the restraints!

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  April 10, 2008 at 9:32 am

  #801055

  You need to understand how NULL works.

  The StatusID columns with a value of null will fail this test:

  StatusID <> '72HOURRESIGNATION'

  Try this:

  ( StatusID <> '72HOURRESIGNATION' or StatusID is null )

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  April 10, 2008 at 9:37 am

  #801056

  Read up on NULL in BOL ...

  Select Count (Distinct ConsultantID) from @t

  Where DeactivationDate = '2007-09-04'

  And ISNULL(StatusID, '') <> '72HOURRESIGNATION'

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg

• alorenzini

  SSCarpal Tunnel

  Points: 4620

  More actions

  April 10, 2008 at 12:47 pm

  #801147

  Thanks guys, I read about NULL and used your suggestion and it worked.

  Thanks,
  Art
  Database Analyst
  Tastefully Simple, Inc.

  alorenzini@tastefullysimple.com
  " Some days, it's not even worth chewing through the restraints!