Reputation: 1708
How can I find a value that doesn't exist in a table?
I have a simplified table called Bookings that has two columns BookDate and BookSlot. The BookDate column will have dates only (no time) and the BookSlot column will contain the time of the day in intervals of 30 minutes from 0 to 1410 inclusive. (i.e. 600 = 10:00am)
How can I find the first slot available in the future (not booked) without running through a loop?
Here is the table definition and test data:
Create Table Bookings(
BookDate DateTime Not Null,
BookSlot Int Not Null
)
Go
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',0);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',30);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',630);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',90);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',120);
I want a way to return the first available slot that is not in the table and that is in the future (based on server time).
Based on above test data:
- If the current server time was 1st Jul, 00:10am, the result should be 1st Jul, 90min (01:30am).
- If the current server time was 2nd Jul, 01:05am, the result should be 2nd Jul, 150min (02:30am).
If there are no bookings in the future, the function would simply return the closest half-hour in the future.
--
SQL Fiddle for this is here:
Upvotes: 5
Views: 433
Answers (5)
Reputation: 4137
It's a bit complicated but try this:
WITH DATA
AS (SELECT *,
Row_number()
OVER (
ORDER BY BOOKDATE, BOOKSLOT) RN
FROM BOOKINGS)
SELECT CASE
WHEN T.BOOKSLOT = 1410 THEN Dateadd(DAY, 1, BOOKDATE)
ELSE BOOKDATE
END Book_Date,
CASE
WHEN T.BOOKSLOT = 1410 THEN 0
ELSE BOOKSLOT + 30
END Book_Slot
FROM (SELECT TOP 1 T1.*
FROM DATA T1
LEFT JOIN DATA t2
ON t1.RN = T2.RN - 1
WHERE t2.BOOKSLOT - t1.BOOKSLOT > 30
OR ( t1.BOOKDATE != T2.BOOKDATE
AND ( t2.BOOKSLOT != 0
OR t1.BOOKSLOT != 630 ) )
OR t2.BOOKSLOT IS NULL)T
Here is the SQL fiddle example.
Explanation
This solution contains 2 parts:
- Comparing each line to the next and checking for a gap (can be done easier in SQL 2012)
- Adding a half an hour to create the next slot, this includes moving to the next day if needed.
Edit
Added TOP 1 in the query so that only the first slot is returned as requested.
Update
Here is the updated version including 2 new elements (getting current date+ time and dealing with empty table):
DECLARE @Date DATETIME = '2014-07-01',
@Slot INT = 630
DECLARE @time AS TIME = Cast(Getdate() AS TIME)
SELECT @Slot = Datepart(HOUR, @time) * 60 + Round(Datepart(MINUTE, @time) / 30,
0) * 30
+ 30
SET @Date = Cast(Getdate() AS DATE)
;WITH DATA
AS (SELECT *,
Row_number()
OVER (
ORDER BY BOOKDATE, BOOKSLOT) RN
FROM BOOKINGS
WHERE BOOKDATE > @Date
OR ( BOOKDATE = @Date
AND BOOKSLOT >= @Slot ))
SELECT TOP 1 BOOK_DATE,
BOOK_SLOT
FROM (SELECT CASE
WHEN RN = 1
AND NOT (@slot = BOOKSLOT
AND @Date = BOOKDATE) THEN @Date
WHEN T.BOOKSLOT = 1410 THEN Dateadd(DAY, 1, BOOKDATE)
ELSE BOOKDATE
END Book_Date,
CASE
WHEN RN = 1
AND NOT (@slot = BOOKSLOT
AND @Date = BOOKDATE) THEN @Slot
WHEN T.BOOKSLOT = 1410 THEN 0
ELSE BOOKSLOT + 30
END Book_Slot,
1 AS ID
FROM (SELECT TOP 1 T1.*
FROM DATA T1
LEFT JOIN DATA t2
ON t1.RN = T2.RN - 1
WHERE t2.BOOKSLOT - t1.BOOKSLOT > 30
OR ( t1.BOOKDATE != T2.BOOKDATE
AND ( t2.BOOKSLOT != 0
OR t1.BOOKSLOT != 1410 ) )
OR t2.BOOKSLOT IS NULL)T
UNION
SELECT @date AS bookDate,
@slot AS BookSlot,
2 ID)X
ORDER BY X.ID
Play around with the SQL fiddle and let me know what you think.
Upvotes: 1
Reputation: 11893
Using a tally table to generate a list of originally available booking slots out 6 weeks (adjustable below):
declare @Date as date = getdate();
declare @slot as int = 30 * (datediff(n,@Date,getdate()) /30);
with
slots as (
select (ROW_NUMBER() over (order by s)-1) * 30 as BookSlot
from(
values (1),(1),(1),(1),(1),(1),(1),(1) -- 4 hour block
)slots(s)
cross join (
values (1),(1),(1),(1),(1),(1) -- 6 blocks of 4 hours each day
)QuadHours(t)
)
,days as (
select (ROW_NUMBER() over (order by s)-1) + getdate() as BookDate
from (
values (1),(1),(1),(1),(1),(1),(1) -- 7 days in a week
)dayList(s)
cross join (
-- set this to number of weeks out to allow bookings to be made
values (1),(1),(1),(1),(1),(1) -- allow 6 weeks of bookings at a time
)weeks(t)
)
,tally as (
select
cast(days.BookDate as date) as BookDate
,slots.BookSlot as BookSLot
from slots
cross join days
)
select top 1
tally.BookDate
,tally.BookSlot
from tally
left join #Bookings book
on tally.BookDate = book.BookDate
and tally.BookSlot = book.BookSlot
where book.BookSlot is null
and ( tally.BookDate > @Date or tally.BookSlot > @slot )
order by tally.BookDate,tally.BookSlot;
go
Upvotes: 0
Reputation: 46233
Below is one method that will allow bookings up to 256 days in the future, and allow for an empty Booking table. I assume you are using SQL Server 2005 since your BookDate is dateTime instead of date.
In any case, you might consider storing the slots as a complete datetime instead of separate columns. That will facilitate queries and improve performance.
DECLARE @now DATETIME = '2014-07-01 00:10:00';
WITH T4
AS (SELECT N
FROM (VALUES(0),
(0),
(0),
(0),
(0),
(0),
(0),
(0)) AS t(N)),
T256
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT 0)) - 1 AS n
FROM T4 AS a
CROSS JOIN T4 AS b
CROSS JOIN T4 AS c),
START_DATE
AS (SELECT Dateadd(DAY, Datediff(DAY, '', @now), '') AS start_date),
START_TIME
AS (SELECT Dateadd(MINUTE, Datediff(MINUTE, '', @now) / 30 * 30, '') AS
start_time),
DAILY_INTERVALS
AS (SELECT N * 30 AS interval
FROM T256
WHERE N < 48)
SELECT TOP (1) Dateadd(DAY, future_days.N, START_DATE) AS BookDate,
DAILY_INTERVALS.INTERVAL AS BookSlot
FROM START_DATE
CROSS APPLY START_TIME
CROSS APPLY DAILY_INTERVALS
CROSS APPLY T256 AS future_days
WHERE Dateadd(MINUTE, DAILY_INTERVALS.INTERVAL,
Dateadd(DAY, future_days.N, START_DATE)) > START_TIME
AND NOT EXISTS(SELECT *
FROM DBO.BOOKINGS
WHERE BOOKDATE = START_DATE
AND BOOKSLOT = DAILY_INTERVALS.INTERVAL)
ORDER BY BOOKDATE,
BOOKSLOT;
See this SQL Fiddle
Upvotes: 1
Reputation: 1270391
In SQL Server 2012 and later, you can use the lead() function. The logic is a bit convoluted because of all the boundary conditions. I think this captures it:
select top 1
(case when BookSlot = 1410 then BookDate else BookDate + 1 end) as BookDate,
(case when BookSlot = 1410 then 0 else BookSlot + 30 end) as BookSlot
from (select b.*,
lead(BookDate) over (order by BookDate) as next_dt,
lead(BookSlot) over (partition by BookDate order by BookSlot) as next_bs
from bookings b
) b
where (next_bs is null and BookSlot < 1410 or
next_bs - BookSlot > 30 or
BookSlot = 1410 and (next_dt <> BookDate + 1 or next_dt = BookDate and next_bs <> 0)
)
order by BookDate, BookSlot;
Upvotes: 0
Reputation: 10875
try this:
SELECT a.bookdate, ((a.bookslot/60.)+.5) * 60
FROM bookings a LEFT JOIN bookings b
ON a.bookdate=b.bookdate AND (a.bookslot/60.)+.50=b.bookslot/60.
WHERE b.bookslot IS null
Upvotes: -1
Related Questions
- SQL to fetch value of one column such that a certain value in another column does not exist
- How to identify entries where a data does not exist in a table with a condition
- Finding a value in one table which doesn't exist in another
- Query that returns data not found in a table
- Find values which are not present in the table
- SQL How to find the Data which is not in table
- SQL Query - Finding records where value doesn't exist
- select a value where it doesnt exist in another table in SQL Server?
- SQL Server Select a row for where a column value does not exists
- SQL Server: Find Values that don't exist in a table