Display Column value based on condition in Join query using Case statement in SQL Server

Hi nadeem1218,

Refer below sample.

SQL

DECLARE @TestTable AS TABLE(ID INT, Name VARCHAR(20),CheckIn DATETIME,CheckOut DATETIME)
INSERT INTO @TestTable VALUES(1,'John Hammod','2019/06/12','2019/06/13')
INSERT INTO @TestTable VALUES(2,'Mudassar Khan','2019/06/13','2019/06/14')
INSERT INTO @TestTable VALUES(3,'Suzanne Mathews','2019/06/14','2019/06/15')
INSERT INTO @TestTable VALUES(4,'Robert Schidner',NULL,NULL)
SELECT ID,Name,CheckIn,CheckOut,CASE
WHEN CheckIn IS NULL AND CheckOut IS NULL  THEN 'ABSENT'
ELSE 'PRESENT'
END 'IsAbsent'
FROM @TestTable

Hi Pandeyism,

Thanks for the answer. But there is one issue if you see my query, it has multiple sub-queires and joins. Im not sure this approach will work.

Regards,

Nadeem

Hi nadeem1218,

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

Refer below sample query.

SQL

SELECT c.CustomerID, c.CompanyName,c.Country,
CASE WHEN c.Region IS NULL THEN 'Absent'
ELSE 'PRESENT' END  'IsAbsent'
FROM Orders o 
INNER JOIN Customers c ON c.CustomerID = o.CustomerID