I am having trouble finding the available time for HALF-an-hour and One-hour time slots for a particular Treatment - on a selected date. User has options of selecting HALF-an-hour and One-hour at the booking end example = On a selected date - there is a booking for 9am for 1 hour (9am to 10am) and there is another booking - 11am for HALF-an-hour (11am to 11:30am) then the user should not see these two slots on the same selected day He should get this on the display(after selecting the treater and date)

Half an Hour:

9am to 930 am ❌ (not available)
930am to 10am ❌ (not available)
10-am -to 1030 ✅ (available)
1030 to 11am ✅ (available)
11am to 11:30 ❌ (not available)
11:30 to 1200pm ✅ (available) and so on................... .

One Hour
9am to 10am ❌ (not available)
10am to 11am ✅ (available)
11am to 12pm ❌ (not available), (((((here if possible we ca make 1130am to 12:30pm ✅ (available), and then sequence continues from 12:30 and so on....)))
12pm to 1pm ✅ (available)
1pm to 2pm ✅ (available) and so on---------------------------

I tried to do it like this.

I created two tables - one for HALF-an-hour slots and - one for ONE-hour slots.

these two tables have timebegin and timeEnd

I have another table that has the booked entries.

I tried with EXCEPT in SQl - but that seems to be giving wrong results

	SELECT T1.timeBegin from ClinicNew.HalfTiming T1 
		left join  ClinicNew.FullTiming T2
		On T1.TimeBegin=T2.TimeBegin
		EXCEPT
		select distinct T1.timeBegin from ClinicNew.HalfTiming T1 
		inner join ClinicNew.NewTreaterEngagedDTM T2
		On T1.timeBegin = T2.timeBegin
		where T2.BookedDate = '2014-04-15'
		and T2.TreaterID=

Please help

I think you're probably overcomplicating this by having multiple tables for time slots of different lengths. What happens when you want to go to 15-minute intervals rather than 30? What happens when you want to allow 90-minute appointments? What happens if the office scheduling these appointments has different hours on different days?

The solution I propose below uses one table to store appointments, and that's it. The rest of the logic shown could easily go into a stored procedure or something that you call when you want a list of available appointments for a given date. Hopefully the comments are sufficient to explain what's going on.

-- Sample data from the question.
declare @Appointment table
(
    [ID] bigint not null identity(1, 1), -- Primary key.
    [BookedDate] date not null,          -- The date of the appointment.
    [Time] time(0) not null,             -- The start time of the appointment.
    [Duration] int not null              -- The length of the appointment in minutes.
);
insert @Appointment
    ([BookedDate], [Time], [Duration])
values
    ('2014-04-15', '09:00', 60),
    ('2014-04-15', '10:00', 30),
    ('2014-04-15', '17:00', 60),
    ('2014-04-15', '18:30', 30);

-- @StartTime is the time the office opens on the desired date.
-- @EndTime is the time the office closes on the desired date.
-- @Interval is the number of minutes that separate potential appointment times.
-- @DesiredDate is the date on which an appointment is requested.
-- @DesiredLength is the length of the requested appointment in minutes.
declare @StartTime time(0) = '09:00';
declare @EndTime time(0) = '21:00';
declare @Interval int = 30;
declare @DesiredDate date = '2014-04-15';
declare @DesiredLength int = 30;

-- This CTE enumerates all potential timeslots on the @DesiredDate given the above data.
with [TimeSlotCTE] as
(
    -- Base case: the first appointment slot of the day.
    select 
        [From] = @StartTime, 
        [To] = dateadd(minute, @DesiredLength, @StartTime)

    union all

    -- Recursive case: create a subsequent appointment slot as long as doing so won't
    -- take us past the office's closing time.
    select
        dateadd(minute, @Interval, [From]),
        dateadd(minute, @Interval, [To])
    from
        [TimeSlotCTE]
    where
        dateadd(minute, @Interval, [To]) <= @EndTime
)

-- Finally, we simply select every time slot defined above for which there does not
-- yet exist an overlapping appointment on the requested date.
select
    [T].[From],
    [T].[To],
    [Available] = 
        case when exists 
        (
            select 1 from @Appointment [A]
            where
                -- Forgot this line the first time around!
                [A].[BookedDate] = @DesiredDate and
                [A].[Time] < [T].[To] and
                dateadd(minute, [A].[Duration], [A].[Time]) > [T].[From]
        )
        then 'No' else 'Yes' end
from
    [TimeSlotCTE] [T];

Here's the output if I run the above code with @DesiredLength = 30:

From        To          Available
09:00:00    09:30:00    No
09:30:00    10:00:00    No
10:00:00    10:30:00    No
10:30:00    11:00:00    Yes
11:00:00    11:30:00    Yes
11:30:00    12:00:00    Yes
12:00:00    12:30:00    Yes
12:30:00    13:00:00    Yes
13:00:00    13:30:00    Yes
13:30:00    14:00:00    Yes
14:00:00    14:30:00    Yes
14:30:00    15:00:00    Yes
15:00:00    15:30:00    Yes
15:30:00    16:00:00    Yes
16:00:00    16:30:00    Yes
16:30:00    17:00:00    Yes
17:00:00    17:30:00    No
17:30:00    18:00:00    No
18:00:00    18:30:00    Yes
18:30:00    19:00:00    No
19:00:00    19:30:00    Yes
19:30:00    20:00:00    Yes
20:00:00    20:30:00    Yes
20:30:00    21:00:00    Yes

Here it is with @DesiredLength = 60:

From        To          Available
09:00:00    10:00:00    No
09:30:00    10:30:00    No
10:00:00    11:00:00    No
10:30:00    11:30:00    Yes
11:00:00    12:00:00    Yes
11:30:00    12:30:00    Yes
12:00:00    13:00:00    Yes
12:30:00    13:30:00    Yes
13:00:00    14:00:00    Yes
13:30:00    14:30:00    Yes
14:00:00    15:00:00    Yes
14:30:00    15:30:00    Yes
15:00:00    16:00:00    Yes
15:30:00    16:30:00    Yes
16:00:00    17:00:00    Yes
16:30:00    17:30:00    No
17:00:00    18:00:00    No
17:30:00    18:30:00    No
18:00:00    19:00:00    No
18:30:00    19:30:00    No
19:00:00    20:00:00    Yes
19:30:00    20:30:00    Yes
20:00:00    21:00:00    Yes

Will something like this work for you?