How to perform Case statement inside a select statement?-CodePudding

I wanted to put 'No record' on the column instead of NULL if the datediff function returns a null value.

SELECT concat(e.firstname ,e.lastname) as Fullname,c.shiftcode as Shift, cast(c.datecheckinout as date) Date,datename(month, c.datecheckinout) as RecordMonth,c.timein , c.timeout,
CAST( 
    CASE
    WHEN (datediff(HOUR,c.timein,c.timeout)  IS NULL) 
    THEN 'No record'
    END 
    ), FROM tblCheckInOutDetail c  RIGHT JOIN tblEmployee e on e.IdEmployee = c.IdEmployee   WHERE e.IdEmployee = 55

So far this code only throws Incorrect syntax near 'CAST', expected 'AS'. but I don't know what data type should I put in the CAST parameter , since if there's a record it will show the datetime .

CodePudding user response：

You need to convert the number value to a string. For this, you can use coalesce():

SELECT concat(e.firstname ,e.lastname) as Fullname,c.shiftcode as Shift, cast(c.datecheckinout as date) Date,datename(month, c.datecheckinout) as RecordMonth,c.timein , c.timeout,
       COALESCE(CAST(datediff(HOUR, c.timein, c.timeout) AS VARCHAR(255)), 'No record')
FROM tblEmployee e LEFT JOIN
     tblCheckInOutDetail c 
     ON e.IdEmployee = c.IdEmployee
WHERE e.IdEmployee = 55;

Note: I switched the RIGHT JOIN to a LEFT JOIN. They are equivalent logically. But most people find it much easier to follow the logic of the LEFT JOIN, because the table that defines the rows is the first table being read in the FROM clause.

CodePudding user response：

Strictly answering question (though I don't understand why you need a CASE expression if you have working versions of the query), you can easily translate this to a CASE expression:

ISNULL(CAST(datediff(HOUR,c.timein,c.timeout) as varchar),'No Record')

ISNULL really is just nice, convenient shorthand for CASE WHEN a IS NOT NULL THEN a ELSE b END, so:

CASE WHEN DATEDIFF(HOUR, c.timein, c.timeout) IS NOT NULL
  THEN CAST(datediff(HOUR,c.timein,c.timeout) as varchar(11))
  ELSE 'No Record' END

As you can see, a downside is that if you really really really want a CASE expression, you have to repeat at least the DATEDIFF to cover both the case where the outer row doesn't exist and the case where the outer row exists but one of the values is NULL.

Also note that you should always specify a length for variable types like varchar, even in cases where you think you're safe with the default.

CodePudding user response：

I don't know if this is the correct option or usage.

but this works for me :

ISNULL(CAST(datediff(HOUR,c.timein,c.timeout) as varchar),'No Record')

But can you guys show me how to do this using case expression?