how can i count null values or non-null values with range of date using SQL-CodePudding

here's the first thing i thought about

SELECT * FROM DATABASE..TABLE WHERE (FIELDNAME BETWEEN 'DATE' AND 'DATE') AND (COUNT(FIELDNAME2) IS NULL)

i am new to sql

CodePudding user response：

SELECT SUM(CASE WHEN FIELDNAME IS NULL THEN 1 ELSE 0 END) [CountOfNull],
       SUM(CASE WHEN FIELDNAME IS NOT NULL THEN 1 ELSE 0 END) [CountOfNotNull]
FROM DATABASE..TABLE WHERE FIELDNAME IS NULL OR (FIELDNAME BETWEEN 'DATE' AND 'DATE')

CodePudding user response：

I'm not entirely sure whether you're asking for:

Select COUNT(*)
FROM MyTable
WHERE MyDateField IS NULL
      OR (MyDateField BETWEEN 'StartDate' AND 'EndDate');

Which will get you a count of records where your date is either NULL or in the given range.

Or:

SELECT SUM(CASE WHEN FIELDNAME IS NULL THEN 1 ELSE 0 END) [CountOfNull],
       SUM(CASE WHEN FIELDNAME IS NOT NULL THEN 1 ELSE 0 END) [CountOfNotNull]
FROM DATABASE..TABLE
WHERE FIELDNAME IS NULL OR
      (FIELDNAME BETWEEN 'DATE' AND 'DATE')

Which will get you a count of records where the date is null, and a count of records where it falls in the given range.