Ignore date list from CTE

Question

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

Answer 1

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))

Answer 2

ok try this,

create one more cte,

,cte2 as
(
select * from @Holidays
union all
select BookingID,StartdateTime,EndDateTime from @EventBooking
)

then as usual

AND not exists (select 1
                from cte2 h
                where cast(a.RepeatEventDate as date) between cast(h.startdatetime as date) and cast(h.enddatetime as date)
               )

this one is latest (datetime conversion very confusing,i just started from @Gordon query.

AND not exists (select 1
                from cte2 h
                where cast(DATEADD(SECOND, DATEDIFF(SECOND, 0, StartTime), RepeatEventDate) as datetime) between cast(h.startdatetime as datetime) and cast(h.enddatetime as datetime)
               )

Answer 3

Please try:

select * From StaffSchedule 
where ID not in(
        select 
            ID   
        From StaffSchedule a inner join 
        (
            select StartdateTime, EndDateTime From dbo.BookedSchedules
            union all
            select StartdateTime, EndDateTime From dbo.Holidays
        )b  on a.StartdateTime between b.StartdateTime and b.EndDateTime and 
            a.EndDateTime between b.StartdateTime and b.EndDateTime)

Chekck SQL Fiddle Demo

Answer 4

RESULT= CTE - BookedSchedule - Holidays

Will be equal to use set theories subtract operation, in sql server you may use Except (Minus in Oracle).

   select  StaffId, StartdateTime,EndDateTime  from StaffSchedule -- CTE 
    except
    (select  StaffId, StartdateTime,EndDateTime  from BookedSchedules) -- BookedSchedule 
    except
    (select StaffSchedule.StaffId, StaffSchedule.StartdateTime , StaffSchedule.EndDateTime
    from StaffSchedule 
    inner join Holidays 
    on 
    cast(Holidays.StartdateTime As Date) = cast(StaffSchedule.StartdateTime As Date)
    and 
    cast(Holidays.EndDateTime As Date) = cast(StaffSchedule.EndDateTime As Date) 
    )                                                                    -- Holidays
    ;

Sqlfiddle demo

If a multi-day holiday could be inserted, like:

INSERT dbo.Holidays (StartdateTime, EndDateTime)
VALUES 
    ('2014-03-05 00:00:00.000', '2014-03-07 23:59:00.000');

Using the query bellow to extract staff-holidays will be useful:

(select StaffSchedule.StaffId, StaffSchedule.StartdateTime , StaffSchedule.EndDateTime
from StaffSchedule 
inner join Holidays 
on 
cast(Holidays.StartdateTime As Date) <= cast(StaffSchedule.StartdateTime As Date)
and 
cast(Holidays.EndDateTime As Date) >= cast(StaffSchedule.EndDateTime As Date) 
)