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

Last Reply 2 months ago By pandeyism

Posted 2 months ago

Hello,

I have a sql qeury which fetches a day's attendance for all Employees.

https://imgur.com/a/RT363z2

Now, I need to add one a conditional column column (IsAbsent) where if both the check-in & check-out are null it should show as 'Absent' else '-'

Below is my query.

use [zkteco_biotime]

SELECT cast(UI.badgenumber as int) as [Ac No], EB.NAME as [Name],d2.deptname,
Cast(sc.AttDate as DATE)as Date,
(select [Check-In] from dbo.FILOReport  where dbo.FILOReport.pin= UI.badgenumber and checktime =sc.AttDate) as [Check-In],
(select [Check-Out] from dbo.FILOReport  where dbo.FILOReport.pin= UI.badgenumber and checktime =sc.AttDate) as [Check-Out]
FROM userinfo UI LEFT OUTER JOIN attshifts Sc ON ui.userid = sc.userid INNER JOIN
departments dpt ON UI.defaultdeptid = dpt.DeptID INNER JOIN departments d2 on dpt.supdeptid=d2.DeptID
INNER JOIN EmpBaan EB ON cast(UI.badgenumber as int) = EB.EMPID
where dpt.supdeptid=15
and sc.AttDate=CAST(getdate()-1 as date) order by badgenumber asc
You are viewing reply posted by: pandeyism 2 months ago.
Posted 2 months ago Modified on 2 months ago

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