0

I am using 

declare @starttime datetime = '2015-10-28 10:00', 
        @endtime datetime = '2015-10-28 12:00', 
        @interval int = 30

I would like to retrieve these values from a table instead of declaring them as static values. I have a separate table that has these 3 values (Startdate, Enddate, interval).

I tried creating a temp table an then inserting values into it but the naming convention fails to work if I want to use the vales in my further query. My entire query is this and in this i want the startdate, enddate,interval to be fetched from the database instead of being static.

create table #booking (start datetime, [end] datetime)
 insert into #booking values 
 ('2015-10-28 08:00','2015-10-28 08:30'),
 ('2015-10-28 10:00','2015-10-28 10:30'),
 ('2015-10-28 10:30','2015-10-28 11:00')

 declare @starttime datetime = '2015-10-28 08:00',
         @endtime datetime = '2015-10-28 12:00', 
         @interval int = 30, 
         @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 [end]
 into
     #slots
 from
     #numbers

 select s.*, b.* from #slots s
 left join #booking b
 on s.start = b.start and s.[end] = b.[end]
 where b.start is null

 drop table #numbers, #booking, #slots
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fahad Ashraf
  • 75
  • 1
  • 9

2 Answers2

1

I'm not so sure I understand the question, but assuming you can fetch a single row from that table, you could just do this:

declare @starttime datetime
       ,@endtime datetime
       ,@interval int 

select @starttime = starttime
      ,@endtime = endtime 
      ,@interval = interval
 from yourTable
 where <condition>
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
0

You can do something like:

SELECT
  @starttime = Startdate
, @endtime   = Enddate
, @interval  = interval
FROM  YourTableHere
Cano
  • 11
  • 4