Q:

Ignore date list from CTE

CTE gives a below result
    Name            | StartDateTime           |  EndDateTime        
--------------------+-------------------------+------------------------
Hair Massage        | 2014-02-15 09:00:00.000 | 2014-02-15 10:00:00.000
Hair Massage        | 2014-02-15 10:00:00.000 | 2014-02-15 11:00:00.000
(X)Hair Massage     | 2014-02-23 09:00:00.000 | 2014-02-23 10:00:00.000
(X)Hair Cut         | 2014-02-20 12:15:00.000 | 2014-02-20 13:00:00.000
Hair Cut            | 2014-03-07 11:30:00.000 | 2014-03-07 12:15:00.000

Also I have Holidays
    Id       | StartDateTime      |  EndDateTime        
-------------+--------------------+-------------------
    1        | 20140223 00:00:00  | 20140224 23:59:00

And EventBooking
    EventId  | StartDateTime           |  EndDateTime        
-------------+-------------------------+------------------------
    1        | 2014-02-20 12:15:00.000 | 2014-02-20 13:00:00.000

I want to remove the dates falls under holidays and EventBooking from my CTE.
I mean remove the (X) recods from my CTE
RESULT=CTE- BookedSchedule-Holidays
with HoliDaysCte2 as
(
select StartdateTime,EndDateTime from Holidays
union all
select StartdateTime,EndDateTime from EventBooking
)

SELECT
    Name, 
    StartDateTime, 
    EndDateTime

FROM CTE WHERE not exists (select 1
                from HoliDaysCte2 h
                where cast(a.RepeatEventDate as DATETIME) between 
                   cast(h.startdatetime as DATETIME) 
                   and cast(h.enddatetime as DATETIME)
               )

Here is my SQL FIDDLE DEMO

A:

Okay Assuming this is your schema
CREATE TABLE dbo.StaffSchedule
(       ID INT IDENTITY(1, 1) NOT NULL,
        Name Varchar(50),       
        StartdateTime DATETIME2 NOT NULL,
        EndDateTime DATETIME2 NOT NULL
);

CREATE TABLE dbo.BookedSchedules
(       ID INT IDENTITY(1, 1) NOT NULL,
        StaffId INT,        
        StartdateTime DATETIME2 NOT NULL,
        EndDateTime DATETIME2 NOT NULL
);

CREATE TABLE dbo.Holidays
(       ID INT,
        StartdateTime DATETIME2 NOT NULL,
        EndDateTime DATETIME2 NOT NULL
);
INSERT dbo.StaffSchedule (Name, StartdateTime, EndDateTime)
VALUES 
    ('Hair Massage','2014-02-15 09:00:00.000','2014-02-15 10:00:00.000'),
    ('Hair Massage','2014-02-15 10:00:00.000','2014-02-15 11:00:00.000'),
    ('(X)Hair Massage','2014-02-23 09:00:00.000','2014-02-23 10:00:00.000'),
    ('(X)Hair Cut','2014-02-20 12:15:00.000','2014-02-20 13:00:00.000'),
    ('Hair Cut','2014-03-07 11:30:00.000','2014-03-07 12:15:00.000');

INSERT dbo.BookedSchedules (StaffId, StartdateTime, EndDateTime)
VALUES 
(1,'2014-02-20 12:15:00.000','2014-02-20 13:00:00.000');

INSERT dbo.Holidays (ID,StartdateTime, EndDateTime)
VALUES 
(1,'20140223 00:00:00','20140224 23:59:00');

Does this solves your issue?
select * from StaffSchedule SS
where 
not exists(
select * from NonBookingSlots NBS
where (dateadd(MICROSECOND,1,ss.StartdateTime) 
    between nbs.StartdateTime and nbs.EndDateTime) 
        or (dateadd(MICROSECOND,-1,ss.EndDateTime) 
    between nbs.StartdateTime and nbs.EndDateTime))

