how to omit null values using SQL query

Question

I am trying to only display the rows in which there is date for Researchers. I cannot manage to omit the rows with Null Values. I even tried this solution How to remove null rows from sql query result?..

This is my Query:

SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status;

This is my Current output:

any suggestion. Thank you

Lukasz Szozda · Accepted Answer · 2015-10-03 19:46:22Z

Quickfix, without reading query:

WITH cte AS
(
SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status
)
SELECT *
FROM cte
WHERE Researchers IS NOT NULL;

There is probably more elegant solution, but you need to share sample data and structures.

This part may cause problems:

SELECT DISTINCT ', ' + r.Surname

try with CONCAT instead or :

SELECT DISTINCT ', ' + ISNULL(r.Surname, '')

Gordon Linoff · Accepted Answer · 2015-10-04 13:19:59Z

1

You should filter out the researchers before the group by rather than afterwards. When possible, it is better (performance-wise) to put conditions before aggregation.

SELECT s.Title AS Submission_Title, CA.Surname AS Researchers, s.Status 
FROM Submission s CROSS APPLY
     (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
                    FROM ResearcherSubmission rs INNER JOIN
                         Researcher r 
                         ON r.ResearcherID = rs.ResearcherID 
                    WHERE s.SubmissionID = rs.SubmissionID 
                    FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'
                   ), 1, 2, ' '))
    ) AS CA(Surname) 
WHERE s.SubmissionDate >= '2015-10-01' AND s.SubmissionDate < '2015-11-01' AND
      ca.Surname IS NULL
GROUP BY YEAR(s.SubmissionDate), MONTH(s.SubmissionDate), s.Title, CA.Surname, s.Status;

Note the changes made:

Table aliases make the query easier to write and to read.
I changed the date comparison to have no functions on the date itself. This would allow SQL Server to use an index, if appropriate.
I also moved the date comparison from the CROSS APPLY subquery to the outer query. This could be a big gain in efficiency. Why do the extra work for rows that will be filtered out anyway?
I added the NOT NULL condition to the WHERE clause.
The date key in the outer GROUP BY is redundant because the query is only using one month of data. I simplified the logic but left it.

edited Oct 4, 2015 at 13:19

answered Oct 3, 2015 at 22:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Kivan Ilangakoon Over a year ago

Thank you Gordon. I see how this solution could be better performance-wise. However there a few issues that I have encountered and I cant figure out why. Firstly I have left my date as 'October 2015' on purpose as I am using a datetime picker to drill-down a report and display results monthly. So I will later replace 'October 2015' with @0. I also get an error when I run the query saying 'STUFF' is not a recognized function name. I also think there is an extra bracket at the line , 1, 2, ' ')). I could be wrong. I am very new to SQL so sorry for all the questions

Gordon Linoff Over a year ago

@KivanIlangakoon . . . I messed up on the SELECT STUFF(). I usually put it in a correlated subquery, so I just missed the CROSS APPLY -- and it makes sense like that for your purposes (because you don't want empty lists). If this is going into an application, I would suggest that you map user into to date ranges in the app, so the query can be better optimized.

Collectives™ on Stack Overflow

how to omit null values using SQL query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related