I have two tables. One that has the slot info and the other that saves the slot that is selected. Rule![alt text][1]
Booking table![alt text][2] now The code I am using is declare @RuleId int,@starttime varchar(50),@endtime varchar(50),@r int,@interval int SELECT @RuleId=Rules.Rule_Serno, @starttime = Rules.from_dt , @endtime = Rules.to_dt ,@r=Rules.room, @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 @room as RoomId,@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 and b.Service_Id=@ServiceId and b.Room_Id=@room drop table #numbers, #slots The issue is the code only works if the slots are precisely the minutes described in the table .. For example if the slot is for 60 minutes ex:if I book that and if I book manually 7:00 to 7:30 it still shows the 7:00-8:00 slot empty. I need to show anything between the slot also reserved if it is manually done beside the reserved slot. [1]: /storage/temp/3595-11121.jpg [2]: /storage/temp/3596-2221.jpg
