Appointment Slots not working

Question

I have three tables.

Table 1.(Booking)

        CREATE TABLE [dbo].[Booking](
            [Booking_Serno] [int] IDENTITY(1,1) NOT NULL,
            [Dt] [datetime] NULL,
            [start] [nvarchar](50) NULL,
            [todate] [nvarchar](50) NULL,
            [Service_Id] [int] NULL
        ) ON [PRIMARY]

INSERT [dbo].[Booking] ([Booking_Serno], [Dt], [start], [todate], [Service_Id]) VALUES (1, CAST(0x0000A6DA00000000 AS DateTime), N'9:30 AM', N'10:00 AM', 1)
        GO

Table 2.(Service)

CREATE TABLE [dbo].[Service](
    [Service_Serno] [int] IDENTITY(1,1) NOT NULL,
    [Service_Duration] [int] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Service] ON
INSERT [dbo].[Service] ([Service_Serno], [Service_Duration]) VALUES (1, 30)

Table 3 (Rules)

CREATE TABLE [dbo].[Rules](
    [Rule_Serno] [int] IDENTITY(1,1) NOT NULL,
    [Start_Dt] [varchar](50) NULL,
    [End_Dt] [varchar](50) NULL,
    [from_dt] [varchar](50) NULL,
    [to_dt] [varchar](50) NULL,
    [Service_Id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Rules] ON
INSERT [dbo].[Rules] ([Rule_Serno], [Start_Dt], [End_Dt], [from_dt], [to_dt], [Service_Id]) VALUES (1, N'2016-07-02', N'2016-07-13', N'07:00', N'17:00', 1)

I am running a stored procedure. It gets me the desired result but then I am trying to book a time by changing the interval, the slots shows empty even if there is a slot booked. Ex. If i am setting a slot for 60 minutes and book a slot from 7:00-8:00 it shows booked(xxx) but when i change the interval to 30 the 7:00-8:00 becomes available. It should actually display 7:00-7:30 and 7:00-8:00 unavailable. the Stored Procedure is

Dt:-12/12/2016 ; ServiceId:-1

    CREATE PROCEDURE [dbo].[RealGetFollowUp] @Dt varchar(50), @ServiceId int
AS

  --declare @starttime datetime = '2015-10-28 12:00', @endtime datetime = '2015-10-28 14:00'
  DECLARE @starttime varchar(50),
          @endtime varchar(50),
          @interval int
  SELECT
    @starttime = Rules.from_dt,
    @endtime = Rules.to_dt,
    @interval = Service.Service_Duration
  FROM Service
  INNER JOIN Rules
    ON Service.Service_Serno = Rules.Service_Id
  WHERE Service.Service_Serno = @ServiceId
  --SELECT * INTO #tmp FROM d;

  DECLARE @slots int

  SELECT
    @slots = DATEDIFF(MINUTE, @starttime, @endtime) / @interval

  SELECT TOP (@slots)
    N = IDENTITY(int, 1, 1) INTO #Numbers
  FROM master.dbo.syscolumns a
  CROSS JOIN master.dbo.syscolumns b;

  SELECT
    DATEADD(MINUTE, ((n - 1) * @interval), @starttime) AS start,
    DATEADD(MINUTE, (n * @interval), @starttime) AS todate INTO #slots
  FROM #numbers

  SELECT
    @Dt AS 'Date',
    LEFT(CONVERT(varchar, s.start, 108), 10) AS Start,
    LEFT(CONVERT(varchar, s.todate, 108), 10) AS 'End',
    CASE
      WHEN b.start IS NULL THEN '-'
      ELSE 'xx'
    END AS Status
  FROM [#slots] AS s
  LEFT JOIN Booking AS b
    ON s.start = b.start
    AND s.todate = b.todate
    AND b.Dt = @Dt


  DROP TABLE #numbers, #slots
GO

I need to check if there is a slot booked in the Booking table and even if i change the interval in the service table, the slot booked in the booking table should be shown as booked.

Answer 1

Change the output SELECT in the sproc to...

SELECT
    @Dt AS 'Date',
    LEFT(CONVERT(varchar, s.start, 108), 10) AS Start,
    LEFT(CONVERT(varchar, s.todate, 108), 10) AS 'End',
    CASE
        WHEN b.start IS NULL THEN '-'
        ELSE 'xx'
    END AS Status
FROM [#slots] AS s
LEFT JOIN Booking AS b
ON (    
        --Range is bigger than the meeting
        (s.start <= b.start
        AND s.todate >= b.todate) 
        OR
        --Range is smaller than the meeting
        (s.start Between b.start and b.toDate
        AND s.todate Between b.start and b.toDate)
    ) 
    AND b.Dt = @Dt