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. If you run the Current script you should get a 09:30:00-10:00:00 with an interval of 30 minutes as blocked. or xxx and if you change the Service_Duration from the service table to 15 minutes, the xxx Disappears.