SQL Server: Report the most recent record if it does not contain NULL

sql check if column is null or empty
sql if null then 0
sql is not null or empty
sql is null or empty
how to select not null columns in sql server
sql where not null
sql select where multiple columns are not null
sql is null or empty in where clause

I have the following table, employees:

employee_name contract_end_date
Nancy         03/01/2019
Nancy         02/01/2019
Nancy         01/01/2019
John          03/01/2019
John          02/01/2019
John          01/01/2019
John          NULL

In the scenario above, I want to report Nancy's most recent record because she is does not have an active record (does not have NULL in the contract_end_date), this is what I have:

SELECT employee_name , MAX(contract_end_date) as contract_end_date 
FROM [dbo].[employees_test]
WHERE contract_end_date IS NOT NULL
GROUP BY employee_name

I am also getting John in the output from the query above:

employee_name   contract_end_date
John            2019-03-01
Nancy           2019-03-01

What should I change in the code to get:

Nancy           2019-03-01

you can use row_number and subquery to filter out the null records as below:

;with cte as (
    select *, row_number() over(partition by employee_name order by contract_end_date desc) as rown from #employee 
    where employee_name not in (
        select employee_name from #employee where contract_end_date is null
    )
)
select employee_name, contract_end_date from cte where rown = 1

Query attached here https://rextester.com/DMG55580

Professional SQL Server 2005 Programming, To be more specific, we're saying include all employee records, and also include those Scan the result and see if you can find the “new” record. 109 NULL It seems that Employee 109 doesn't report to anyone (probably the CEO, eh?) In the scenario above, I want to report Nancy's most recent record because she is does not have an active record (does not have NULL in the contract_end_date), this is what I have: SELECT employee_name , MAX(contract_end_date) as contract_end_date FROM [dbo].[employees_test] WHERE contract_end_date IS NOT NULL GROUP BY employee_name


NULL is excluded in the aggregation functions. You can assign a special value for NULL:

SELECT employee_name , MAX(ISNULL(contract_end_date,N'9999-12-31')) as contract_end_date 
FROM [dbo].[employees_test]
GROUP BY employee_name
HAVING MAX(ISNULL(contract_end_date,N'9999-12-31'))<>N'9999-12-31'

Microsoft Access 2000, Main report A report that contains a subreport control is called the main report. Make-table query An action query that creates a new table. Many-to-many “Is Not Null” criteria finds all records where there is an entry of any kind. ODBC (​Open Database Connectivity) A protocol for accessing data in SQL database servers. Expression must not contain First, Last, Previous, or RunningValue functions. Expression must not contain nested aggregates that specify recursive. For more information, see Aggregate Functions Reference (Report Builder and SSRS) and Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS).


Your query is returning the unique names and max end dates from any record that is not null in the contract_end_date.

The quickest fix I have is to just exclude any names in your table that have a null in the contract_end_date using a subquery.

SELECT  employee_name, MAX(contract_end_date) as "contract_end_date"
FROM    [dbo].[employees_test]
WHERE   employee_name not in (SELECT employee_name
                              FROM   [dbo].[employees_test]
                              WHERE  contract_end_date is null
                              GROUP  BY employee_name);

There is probably a more optimized approach which may or may not be necessary depending on table size.

SQL NULL Values - IS NULL and IS NOT NULL, If a field in a table is optional, it is possible to insert a new record or update a record We will have to use the IS NULL and IS NOT NULL operators instead. I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition (SequenceId). It appears to work only when I specify the Id and the creation date.


You just want TOP (1), you don't need any aggregation:

SELECT TOP (1)
       employee_name,
       contract_end_date
FROM dbo.employees_test
WHERE contract_end_date IS NOT NULL
ORDER BY contract_end_date DESC;

Edit: Crystal ball time:

SELECT TOP (1) WITH TIES
       employee_name,
       contract_end_date
FROM dbo.employees_test
WHERE contract_end_date IS NOT NULL
ORDER BY ROW_NUMBER() OVER (ORDER BY contract_end_date desc);

SQL NULL Values, If a column in a table is optional, we can insert a new record or update an existing We will have to use the IS NULL and IS NOT NULL operators instead. Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULL value, so users can't filter the data for NULL values.


SQL Drawing Blanks? It's NULL a Surprise! – TDAN.com, A developer trying to write queries to populate different reports was going crazy NOTE: This example was constructed using SQL Server 2014. At first glance, all the yellow cells appear to be NULL, but that is not really the case. In reality, the column in Row 2 is blank and the column in Row 3 contain a single space. Same here Rhys, I would use a CTE, or before that table variables to collect the “current” rows, then join again on the table to get the other columns (…and for those out there old enough to remember SQL Server 6.5 or before, ugly subselects) Not only is this less code, but also fewer disk i/o’s.


Testing for NULLS in a Dataset Filter, A dataset filter is evaluated for each row returned by the dataset. records where the StandardCost field does not contain Null, define a dataset  The SQL language has a number of ways to filter record sets. The “where” clause in your select statements is where most people list the business rules that filter out records. You can use “JOIN” statements with SQL in them, but these are usually more difficult to read. The “NOT EXISTS” statement uses a subquery …


Ssrs if field is null, For example, if an employee does not have any phone number, you can store it as an Your SQL Server Reporting Services (SSRS) report has a multi value If a field in a table is optional, it is possible to insert a new record or update a  NULL really means ‘unknown’ rather than nothing, which is why any expression that compare to a NULL value returns NULL, or unknown. Logically, SQL Server evaluates the subquery, replaces it with the list of values it returns, and then evaluates the [NOT] IN condition. For the IN variant of our query, this does not cause a problem because it