Prevent SQL double booking

Question

I'm trying to prevent double booking in a booking system, C# to SQL Server.

I'm using stored procedures and tried this:

alter proc Datumkoll
    @DatumFrom smalldatetime = null,
    @datumTill smalldatetime = null
as
    select RumsNummer 
    from rum 
    where 
        RumsNummer not in (select rumsnummer 
                           from rumsbokning 
                           where datumfrån >= @datumfrom and datumtill <= @datumTill)

 GO

This code will not prevent double booking if the date is set within an already existing booking

Selecting rooms that aren't currently booked won't prevent double booking. Unless, of course, your database engine does not support concurrency or you are using some very pessimistic locking strategy. Its the code where you create the booking that will matter. — Jodrell, Dec 05 '13 at 09:13

Marc Gravell · Answer 1 · 2013-12-05T10:33:19.043

2

Picture the time-windows and the options:

non-overlapping:

[new]
         [existing]

partial overlapping:

[new]                 [new]
  [existing]   [existing]

containment:

    [new]       [-------new---------]
  [existing]         [existing]

Your check only handles the last of these; you need to go back and consider the others. The trick is that there are actually only 3 tests needed:

the new start date is in an existing booking
the new end date is in an existing booking
and existing booking is entirely inside the new booking

which gives us:

where (datumfrån>=@datumfrom and datumtill <= @datumTill)
or (@datumfrom >= datumfrån and @datumfrom <= datumtill)
or (@datumTill >= datumfrån and @datumfrom <= datumtill)

(see also the comments for alternative constructions)

edited Dec 05 '13 at 10:33

answered Dec 05 '13 at 09:12

Marc Gravell

1,026,079
266
2,566
2,900

1

It can be simplified to [this](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) - `datumfrån <= @datumTill and datumtill >= @datumfrom`. – Nikola Markovinović Dec 05 '13 at 10:20
@NikolaMarkovinović I wasn't convinced initially, but on inspection - that seems valid – Marc Gravell Dec 05 '13 at 10:23
1

Assuming `datefrom > datetill` and `@dateFrom > @dateTill` are both always true, then non-conflicting booking are `@dateTill – Remus Rusanu Dec 05 '13 at 10:31
Yes, I'm sorry, this formula gives overlapping ranges. I should have negated it. – Nikola Markovinović Dec 05 '13 at 10:32

score 0 · Accepted Answer · answered Dec 05 '13 at 09:07

select R.RumsNummer
from rum R
where not exists (
            select RB.rumsnummer
            from rumsbokning RB
            where 
                R.RumId = RB.RumId
                and RB.datumfrån >= @datumfrom 
                and RB.datumtill <= @datumTill )

Anyway, you should perform this check again when you're saving a new booking.

score 0 · Answer 3 · answered Dec 05 '13 at 10:28

0

try

  Select rumsnummer 
   from rumsbokning 
   where datumfrån <= @datumTill and datumtill >= @datumfrom

answered Dec 05 '13 at 10:28

huMpty duMpty

14,346
14
60
99

Prevent SQL double booking

3 Answers3