#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2016
    Posts
    1
    Rep Power
    0

    Cannot handle DoubleBooking


    Hi,

    I have an hall booking application below is the structure of the database, I have issues in handling double booking.

    Application should not allow a slot for already booked for an hall this is what i want but in sql cannot cover all the possible scenarios.
    Code:
    CREATE TABLE [hall_booking_master](
    	[hall_id] [int] NOT NULL,
    	[hall_name] [varchar](75) NULL,
    	[hall_code] [varchar](15) NOT NULL,
    	[hall_code_name] [varchar](75) NOT NULL,
    	[floor] [varchar](15) NOT NULL,
    	[wing] [varchar](15) NOT NULL,
    	[pax] [int] NOT NULL,
    	[hall_type] [varchar](15) NOT NULL,
    	[resources] [varchar](max) NOT NULL
    ) ON [PRIMARY]
    
    CREATE TABLE [hall_booking_details](
    	[booking_id] [int] NOT NULL,
    	[visitor_empid] [varchar](10) NOT NULL,
    	[visitor_name] [varchar](150) NOT NULL,
    	[visitor_winnt_name] [varchar](250) NOT NULL,
    	[visitor_IP] [varchar](150) NOT NULL,
    	[booked_hall_id] [varchar](80) NOT NULL,
    	[program_name] [varchar](500) NOT NULL,
    	[checkindate] [smalldatetime] NOT NULL,
    	[checkintime] [smalldatetime] NOT NULL,
    	[checkoutdate] [smalldatetime] NOT NULL,
    	[checkouttime] [smalldatetime] NOT NULL,
    	[chair_person] [varchar](150) NOT NULL,
    	[no_pax_attending] [varchar](10) NOT NULL,
    	[purpose] [varchar](150) NOT NULL,
    	[cli_visit_firsttime] [varchar](10) NOT NULL,
    	[hall_type] [varchar](10) NOT NULL,
    	[spoc_mobile] [varchar](20) NOT NULL,
    	[status] [varchar](50) NOT NULL,
    	[released_by] [varchar](250) NULL,
    	[released_on] [datetime] NULL,
    	[booked_on_date] [smalldatetime] NOT NULL
    ) ON [PRIMARY]
    
    used in between and also ">= and <=" for the checkin and checkout date and times but still facing double booking entries.
    
    select booked_hall_id,checkindate,checkintime,checkoutdate,checkouttime,status from hall_booking_details1
    where status = 'BA' and booked_hall_id = '1'
    AND (checkindate between '2016-06-19 00:00:00' and '6/19/2016')
    AND ((checkintime between '5:29PM' and '1900-01-01 08:30:00') or (checkintime between '1900-01-01 08:30:00' and '5:29PM'))
    AND (checkoutdate between '2016-06-19 00:00:00' and '6/19/2016')
    AND ((checkouttime between '3:30PM' and '1900-01-01 17:30:00') or (checkouttime between '1900-01-01 17:30:00' and '3:30PM'))
    
    select booked_hall_id,checkindate,checkintime,checkoutdate,checkouttime,status from hall_booking_details1
    where status = 'BA' and booked_hall_id = '1'
    AND (checkindate >= '2016-06-19 00:00:00' and checkindate <= '6/19/2016')
    AND ((checkintime >= '2:32PM' and checkintime <= '1900-01-01 08:30:00') or (checkintime >= '1900-01-01 08:30:00' and checkintime <= '2:32PM'))
    AND (checkoutdate >= '2016-06-19 00:00:00' and checkoutdate <= '6/19/2016')
    AND ((checkouttime >= '3:33PM' and checkouttime <= '1900-01-01 17:30:00') or (checkouttime >= '1900-01-01 08:30:00' and checkintime <= '3:33PM'))
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2016
    Location
    United Kingdom
    Posts
    9
    Rep Power
    0
    Hey Pradeep,

    Is you front end doing the checks before updating the record in the database? Have you thought of creating an unique index on the datetime column?

IMN logo majestic logo threadwatch logo seochat tools logo