Sum case nulls and 1900-01-01 00:00:00.000

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  October 31, 2017 at 4:45 am

  #324300

  Hi Guys.

  I have the following as part of my statement and it counts the '1900-01-01 00:00:00.000' fine.  However, it does not also count the NULLs.  Can you help?  The column contains both NULL and '1900-01-01 00:00:00.000'.  Its worth noting i only have read only access to the tables and did not design it. I am just querying the database.

  Thanks

  SUM (CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED

• Thom A

  SSC Guru

  Points: 99130

  More actions

  October 31, 2017 at 4:54 am

  #1965927

  Considering you're doing a COUNT, why not instead do:
  COUNT(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InspectionClosed END)
  NULLs are not counted when using the aggragate, so making those values NULL as well, will exclude them.

  If you want to keep the SUM operator to do a COUNT, then  you could do:
  SUM(CASE WHEN InspectionClosed = '1900-01-01 00:00:00.000' OR InspectionClosed IS NULL THEN 0 ELSE 1 END)
  I do, however, prefer to use COUNT when doing a count. 😉

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  October 31, 2017 at 5:02 am

  #1965928

  Sounds like an ANSI_NULLS setting thing...  Some sample code:

  DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())

  SELECT * FROM @ClosedStuff

  SET ANSI_NULLS ON

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 3

  SET ANSI_NULLS OFF

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1


  The first SELECT SUM... returns 3, the second returns 1.

  Try using:
  SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  October 31, 2017 at 5:13 am

  #1965931

  Thank you guys both work great

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  October 31, 2017 at 8:21 am

  #1965975

  ThomasRushton - Tuesday, October 31, 2017 5:02 AM

  Sounds like an ANSI_NULLS setting thing...  Some sample code:

  DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())

  SELECT * FROM @ClosedStuff

  SET ANSI_NULLS ON

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 3

  SET ANSI_NULLS OFF

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1


  The first SELECT SUM... returns 3, the second returns 1.

  Try using:
  SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED

  Setting ANS_NULLS has been deprecated.  You should not be changing the setting for ANSI_NULLS. SET ANSI_NULLS

  I would rewrite this as followsSELECT SUM(CASE WHEN InspectionClosed <> '1900-01-01 00:00:00.000' THEN 1 ELSE 0 END) AS CLOSED
FROM @ClosedStuff -- returns 1

  The other option is to use the correct test for NULL values:SELECT SUM(CASE WHEN InspectionClosed = '1900-01-01 00:00:00.000' THEN 0 WHEN InspectionClosed IS NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1

  As you can see, this requires two tests, instead of one for the first approach.  NOTE: Both rewrites use the other form of the CASE expression.  I find that I rarely use the simple CASE expression, because I'm rarely testing only one field.
  
  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 31, 2017 at 8:24 am

  #1965976

  Why not use NULLIF with a COUNT?
  
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())

  SELECT COUNT(NULLIF( InspectionClosed, '19000101')) FROM @ClosedStuff


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99130

  More actions

  October 31, 2017 at 8:32 am

  #1965982

  Luis Cazares - Tuesday, October 31, 2017 8:24 AM

  Why not use NULLIF with a COUNT?
  
  DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
  INSERT INTO @ClosedStuff
  VALUES (NULL),(NULL),('19000101'),(GETDATE())

  SELECT COUNT(NULLIF( InspectionClosed, '19000101')) FROM @ClosedStuff


  I always forget about NULLIF... :Whistling:

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• August 29, 2024 at 7:08 am

  This was removed by the editor as SPAM

• August 31, 2024 at 7:09 am

  This was removed by the editor as SPAM

• September 15, 2024 at 7:05 am

  This was removed by the editor as SPAM

• mark60

  Newbie

  Points: 1

  More actions

  November 14, 2024 at 3:01 pm

  #4481739

  Thomas Rushton wrote:

  Sounds like an ANSI_NULLS setting thing...  Some sample code:

  DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);

  INSERT INTO @ClosedStuff

  VALUES (NULL),(NULL),('19000101'),(GETDATE())

  SELECT * FROM @ClosedStuff

  SET ANSI_NULLS ON

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED

  FROM @ClosedStuff -- returns 3

  SET ANSI_NULLS OFF

  SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED

  FROM @ClosedStuff -- returns 1

  The first SELECT SUM... returns 3, the second returns 1.

  Try using:

  SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED

  thanks for share very helpfully post.