SQL Server: auto-generate records in a range with filtering?, Sql Server

Language: Layout:

create table Employee (
    Empid     int         not null identity(1,1) primary key
  , FirstName varchar(64) not null
  , LastName  varchar(64) not null default ''
  , IsActive  bit         not null default 1 -- false if employee shouldn't show as available
);
insert into employee (FirstName, IsActive) values
 ('fdmillion',1)
,('shift expired person',1)
,('inactive person',0)
,('other person',1);
create table Shifts (
    Shiftid     int     not null identity(1,1) primary key
  , Empid       int     not null references dbo.Employee(Empid)
  , startTime   time(0) not null -- time(0) is 3 bytes vs. 5 bytes default precision of 7
  , endTime     time(0) not null
  , expiryDate  date        null -- no availability after this day
  , weekdayMask int     not null -- bitmask M,T,W > 1,2,4
);
insert into shifts values
  (1,'09:00:00','15:00:00',null,112)      /* friday, saturday, sunday */
 ,(2,'06:00:00','18:00:00','20170401',31) /* monday - friday */
 ,(3,'06:00:00','18:00:00','20170420',31) /* monday - friday */
 ,(4,'06:00:00','18:00:00',null,31);      /* monday - friday */
create table Appointment (
    Apptid    int     not null identity(1,1) primary key
  , Empid     int     not null references dbo.Employee(Empid)
  , apptDate  date    not null -- day appointment takes place
  , startTime time(0) not null /* time(0) is 3 bytes vs. 5 bytes default precision of 7 */
  , endTime   time(0) not null
);
insert into appointment values
  (1,'20170416','10:15','10:45')
, (1,'20170416','13:30','13:55');

go
create function dbo.udf_availability_by_empId_Date (
    @empid int
  , @date date
) returns table with schemabinding as return (
/* Weekday mask using weekday names
   to avoid any issue with datefirst and datepart()
   this uses a 7 row lookup table for the bitmask check */
with Mask as (
  select weekday_name, weekday_value
  from (values ('Monday',1) ,('Tuesday',2) ,('Wednesday',4) ,('Thursday',8)
 ,('Friday',16) ,('Saturday',32) ,('Sunday',64))  w (weekday_name,weekday_value))
/* adhoc numbers table to generate range in half hour increments */
, n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
/* adhoc time range table */
, blocks as (
  select top (48) 
      [block]=convert(time(0),dateadd(minute,30*(row_number() over (order by (select 1)) -1),0))
    , [next_block]=convert(time(0),dateadd(minute,30*(row_number() over (order by (select 1))),0))
  from n as deka cross join n as hecto
  order by row_number() over (order by (select 1))
)
select b.block
from dbo.employee e
  inner join dbo.shifts s
    on s.EmpId = e.Empid
  inner join blocks b
   on b.block >= s.starttime
   and b.block < s.endtime
where e.EmpId = @empid
  and e.IsActive = 1
  and isnull(s.ExpiryDate,'20630405') >= @date
  /* get the value to check against the weekday bitmask */
  and s.weekdaymask & (
    select weekday_value
    from Mask
    where weekday_name = datename(weekday,@date)
    ) != 0
  /* check for appointments that overlap the block */
  and not exists (
    select 1
    from dbo.appointment a
    where a.endtime > b.block
      and b.next_block > a.starttime
      and a.apptDate = @date
      and a.Empid = @empid
    )
);
go
/* show availability by empid and date */

select 
    a.block
  , AvailableBlockStartingTime = convert(varchar(10),a.block,100) 
from dbo.udf_availability_by_empId_Date(1,'20170416') as a

/* show all employee availability for a given date */
select 
    e.EmpId
  , e.FirstName
  , e.IsActive
  , a.block
from dbo.Employee e
  outer apply dbo.udf_availability_by_empId_Date(e.EmpId,'20170416') a

go
/* show a weeks worth of availability for a given empid */
declare @empid    int = 1
declare @fromdate date = '20170410';
;with n as (select [Date]=dateadd(day,n,@fromdate) from (values(0),(1),(2),(3),(4),(5),(6)) t(n))
select 
    [Date] = convert(char(10),n.[Date],120)
  , Weekday_Name = datename(weekday,n.[Date]) 
  , a.block
from n
  outer apply dbo.udf_availability_by_empId_Date(@empid,[Date]) a
order by n.[Date]

View schema
Execution time: 0,08 sec, rows selected: 60, rows affected: 10, absolute service time: 0,27 sec