SQL Server: Report the most recent record if it does not contain NULL
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:
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