SQL Server – How to Check for Null End Date in Staff Records

countnullsql-server-2016

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish.

Basically – show me any employee_no that does not have at least one row with a null value in column Finish.

There can be more than one null value but as long as there's one.

Sample database:

Best Answer

SELECT employee_no
FROM source_table
GROUP BY employee_no
HAVING COUNT(*) = COUNT(finish)

Is there a way to include the whole table in that sequence? – Chris

select serverproperty('ProductVersion') as [version], serverproperty('Edition') as [edition] and got Version 13.0.5102.14 of edition Devleoper Edition (64-bit) – Chris

This is (Microsoft) SQL server 2016 SP2. – Akina

WITH cte AS ( SELECT Employee_No,
                     Task,
                     Start,
                     Finish, 
                     MAX(CASE WHEN Finish IS NULL 
                              THEN 1 
                              ELSE 0 
                              END) OVER (PARTITION BY employee_no) have_nulls
              FROM source_table
            )
SELECT Employee_No,
       Task,
       Start,
       Finish
FROM cte
WHERE have_nulls = 0
/* ORDER BY 1,2,3 */

Related Solutions

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN   
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable 
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable 
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1     
END

SELECT DISTINCT * FROM ##nullable 
WHERE hasNulls = 0  

DROP TABLE ##nullable

Sql-server – Dealing with NULL values and EMPTY strings in UNION of two tables

Use ISNULL or COALESCE

SELECT COALESCE(A.NULLABLEFIELD,'') FROM A
UNION
SELECT COALESCE(B.NULLABLEFIELD,'') FROM B

Best Answer

Related Solutions

Sql-server – In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns

Sql-server – Dealing with NULL values and EMPTY strings in UNION of two tables

Related Question