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

Last Reply 3 months ago By pandeyism

Posted 3 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: nadeem1218 3 months ago.
Posted 3 months ago

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