CODEWITHSUNDEEP
sqlsql-servert-sql
user3518032
user3518032

Reputation: 423

Returning message instead of null in case statement

I am using a CASE statement to return Morning, Evening or Not Yet closed on basis of comparison. It works best but not for 'Not Yet Closed'. It returns null which I don't want. I want if no closing date has been inserted then it should return 'Not Yet Closed' instead of NULL.

ALTER PROCEDURE [dbo].[USP_Report_SelectComplaintsByShift] 

    @IsMorningEvening bit

AS
BEGIN

    Begin Try

      Select ComplaintID, ComplaintSubject, Complainants.ComplainantName as Complainant,
       Case When (datepart(hour, Complaints.ClosingDateTime) < 17) then 'Morning'
       When (datepart(hour, Complaints.ClosingDateTime) >= 17) then 'Evening' 
       When ( Complaints.ClosingDateTime = '' ) then 'Not Closed Yet'
       End as ClosingShift
      from Complaints Inner Join Complainants 
      ON Complaints.Complainant_ID = Complainants.ComplainantI

END

Upvotes: 1

Views: 171

Answers (4)

mehdi lotfi
mehdi lotfi

Reputation: 11571

You must use Else in your case statement.

  Select ComplaintID, ComplaintSubject, Complainants.ComplainantName as Complainant,
         Case 
            When (datepart(hour, Complaints.ClosingDateTime) < 17) then 'Morning'
            When (datepart(hour, Complaints.ClosingDateTime) >= 17) then 'Evening' 
            Else 'Not Closed Yet'
         End as ClosingShift
  from Complaints 
  Inner Join Complainants ON Complaints.Complainant_ID = Complainants.ComplainantI

If you want to check your query with NULL Value use following query:

  Select ComplaintID, ComplaintSubject, Complainants.ComplainantName as Complainant,
         Case 
            When (datepart(hour, Complaints.ClosingDateTime) < 17) then 'Morning'
            When (datepart(hour, Complaints.ClosingDateTime) >= 17) then 'Evening' 
            When (Complaints.ClosingDateTime IS NULL) Then 'Not Closed Yet'
         End as ClosingShift
  from Complaints 
  Inner Join Complainants ON Complaints.Complainant_ID = Complainants.ComplainantI

Upvotes: 2

Jesuraja
Jesuraja

Reputation: 3844

Try this:

Use (Complaints.ClosingDateTime = '') condition first in your CASE statement.

SELECT  ComplaintID, ComplaintSubject, Complainants.ComplainantName AS Complainant,
        CASE    WHEN (Complaints.ClosingDateTime IS NULL OR Complaints.ClosingDateTime = '') THEN 'Not Closed Yet'
                WHEN (DATEPART(HOUR, Complaints.ClosingDateTime) < 17) THEN 'Morning'
                WHEN (DATEPART(HOUR, Complaints.ClosingDateTime) >= 17) THEN 'Evening' 
        END AS ClosingShift
FROM    Complaints INNER JOIN
        Complainants ON Complaints.Complainant_ID = Complainants.ComplainantID

Actually, if Complaints.ClosingDateTime = '' then DATEPART(HOUR, Complaints.ClosingDateTime) is 0. So (DATEPART(HOUR, Complaints.ClosingDateTime) < 17) condition satisfied and you will get the result 'Morning'.

Upvotes: 1

Atur
Atur

Reputation: 1770

Please try using COALESCE to avoid null. I suppose following should work

  Select ComplaintID, ComplaintSubject, Complainants.ComplainantName as Complainant,
  COALESCE (( Case When (datepart(hour, Complaints.ClosingDateTime) < 17) then 'Morning'
   When (datepart(hour, Complaints.ClosingDateTime) >= 17) then 'Evening' 
   When ( Complaints.ClosingDateTime = '' ) then 'Not Closed Yet'
   End),'Not Closed Yet') as ClosingShift
  from Complaints Inner Join Complainants 
  ON Complaints.Complainant_ID = Complainants.ComplainantI

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You check with IS NULL and ELSE part:

Try like this

  Select ComplaintID, ComplaintSubject, Complainants.ComplainantName as Complainant,
         Case 
            When (datepart(hour, Complaints.ClosingDateTime) < 17) Then 'Morning'
            When (datepart(hour, Complaints.ClosingDateTime) >= 17) Then 'Evening' 
            When (Complaints.ClosingDateTime IS NULL ) Then 'Not Closed Yet'
            ELSE 'Not Closed Yet'
         End as ClosingShift
  from Complaints 
  Inner Join Complainants ON Complaints.Complainant_ID = Complainants.ComplainantI

Upvotes: 4

Related Questions