[T-SQL] Reservation System – Conflict Search

Actually, this SQL script came up recently due to my current project need. I am trying to build the reservation system. With the object reserved in the database, I provide the interface so that users can search the date/time on the database see if it has conflict.

Here is the SQL script:

select 'bit' = case --final output is actually the 'true' or 'false' only
when b.cf_bit > 0 then 'true' --when b.cf_bit = 0 then 'false' (Not necessary, cause the else condition is 'false')
else 'false'
end
from
(
select count('CF_bit') as cf_bit --count how many records that are conflict with the user selected date/time
from
(
select R_Property_Name, 'CF_Flag' = case --the true search kernel for conflict
when (R_StartTime</span> >= '3/15/2009 12:00:00 AM' and R_EndTime < '7/14/2009 12:00:00 AM') then 'C1'
when (R_StartTime<= '3/15/2009 12:00:00 AM' and R_EndTime >= '7/14/2009 12:00:00 AM') then 'C2'
when (R_StartTime<= '3/15/2009 12:00:00 AM' and R_EndTime <= '7/14/2009 12:00:00 AM') and (R_EndTime> '3/15/2009 12:00:00 AM') then 'P1'
when (R_StartTime >= '3/15/2009 12:00:00 AM' and R_EndTime >= '7/14/2009 12:00:00 AM') and (R_StartTime < '7/14/2009 12:00:00 AM') then 'P2'
else 'NA'
end
from tblBCMRBS where R_Property_Name = 'Room-ID'
) a where a.CF_Flag != 'NA'
) b

and, here is the database table design,

tblmrbs

Final return is,

bit
——-
true

or

bit
——-
false

Also, you can represent is as 0/1 or anything you want. Reference interface is here
tblmrbs_ui

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s