List of Employees who are not in the list one day before and one day after weekend

Question

I need list of those employees who are absent one day before and one day after weekend in a week......like if some is absent in Friday and present on Monday should not be included in the list

Answer 1

Use datepart(weekday, ) to fetch all records relative to monday and friday.

Have a look at SET DATEFIRST function too.

select *
from   your_table
where  datepart(weekday, Date) = 5 
or     datepart(weekday, Date) = 1;

Answer 2

This will list all employee id that are absent on a Friday and the following Monday (+1 week). I set-up a calendar week from mininum date to maximum date from the table and get only Friday and Monday. Then get all empid that has no attendance in any of those dates.

with caldte as (
SELECT  dateadd(day, rn - 1, t.mindte) as dates,
        datepart(weekday, dateadd(day, rn - 1, t.mindte)) as weekday,
        datepart(wk, dateadd(day, rn - 1, t.mindte)) as weeknum
FROM    ( 
  select row_number() OVER ( order by c.object_id ) AS rn
  FROM sys.columns c) rns, 
  (select min(dte) as mindte, max(dte) as maxdte 
   from tbl) t
    WHERE   rn - 1 <= datediff(day, t.mindte, t.maxdte)
    and datepart(weekday, dateadd(day, rn - 1, t.mindte)) in (2, 6)
  )
select distinct empid  
from tbl
where empid not in (
  select t.empid
  from caldte c, tbl t
  where c.dates = t.dte)
order by empid