Hello,
I have a sql qeury which fetches a day's attendance for all Employees.
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
