Retrieve data based on 2 datetime columns - reservation type system

Question

This is driving me insane. I have looked at the accepted answer here, SQL command between two datetimes for table reservation, and although it does work for one instance, it does not work in another.

Let's assume Equipment#1 is already scheduled today between 09:45 - 11:00. Equipment#1 belongs to Activity#1 but so can Equipment#2 and Equipment#3 and those 2 are not booked. Someone wants to book Activity#1 as well, between 10:00-12:00. I need to return all the equipment and whether they are free or busy. In this case Equipment#1 would return Busy and the other 2 Free. In this instance, the query seems to work fine, where it fails is this scenario.

Activity#2 has Equipment#4 and this activity only has the one piece of equipment available. It is currently booked today between 09:30-12:00. Someone wants to book Activity #2 between 10:00-11:00. In this case the query should come back and say it is busy, but it is returning 0 rows in the first part of the UNION and returning one row in the second part saying it is Available. Now, when I add the NOT from the linked post above, it works, but then it doesn't work for the first scenario.

I am confused as to how to go about this appropriately.

Hope this makes sense.

Here is my query. I am doing UNION so I can get the equipment that is also not scheduled.

SELECT
  EventActivities.DateStart,
  EventActivities.DateEnd,
  EventActivities.EventActivityId,
  EventEquipment.FranchiseEquipmentId,
  EventActivities.EventId,
  FranchiseEquipment.Alias,
  'Busy' AS Status,
  FranchiseActivities.HourlyRate1,
  ActivityMaster.Name AS Activity
FROM EventActivities
  INNER JOIN EventEquipment
    ON EventEquipment.EventActivityId = EventActivities.EventActivityId
  INNER JOIN FranchiseEquipment
    ON EventEquipment.FranchiseEquipmentId = FranchiseEquipment.FranchiseEquipmentId
  INNER JOIN FranchiseActivities
    ON EventActivities.FranchiseActivityId = FranchiseActivities.FranchiseActivityId
  INNER JOIN ActivityMaster
    ON FranchiseActivities.MasterActivityId = ActivityMaster.ActivityMasterId
WHERE FranchiseEquipment.FranchiseId = 1
AND EventEquipment.FranchiseEquipmentId IN (2, 4, 7)
AND (EventActivities.DateStart >= '2014-10-06 10:00:00' AND EventActivities.DateStart <= '2014-10-06 11:00:00'
OR EventActivities.DateEnd >= '2014-10-06 10:00:00' AND EventActivities.DateEnd <= '2014-10-06 11:00:00')

UNION

SELECT
  NULL,
  NULL AS expr1,
  NULL AS expr2,
  FranchiseEquipment.FranchiseEquipmentId,
  NULL AS expr3,
  FranchiseEquipment.Alias,
  'Free' AS Status,
  FranchiseActivities.HourlyRate1,
  ActivityMaster.Name AS Activity
FROM FranchiseActivities_FranchiseEquipment
  INNER JOIN FranchiseEquipment
    ON FranchiseActivities_FranchiseEquipment.FranchiseEquipmentId = FranchiseEquipment.FranchiseEquipmentId
  INNER JOIN FranchiseActivities
    ON FranchiseActivities_FranchiseEquipment.FranchiseActivityId = FranchiseActivities.FranchiseActivityId
  INNER JOIN ActivityMaster
    ON FranchiseActivities.MasterActivityId = ActivityMaster.ActivityMasterId
WHERE FranchiseEquipment.FranchiseId = 1
AND FranchiseEquipment.FranchiseEquipmentId IN (2, 4, 7)
AND FranchiseEquipment.FranchiseEquipmentId NOT IN (SELECT
    EventEquipment.FranchiseEquipmentId
  FROM EventActivities
    INNER JOIN EventEquipment
      ON EventEquipment.EventActivityId = EventActivities.EventActivityId
  WHERE EventEquipment.FranchiseEquipmentId IN (2, 4, 7)
  AND (EventActivities.DateStart >= '2014-10-06 10:00:00' AND EventActivities.DateStart <= '2014-10-06 11:00:00'
  OR EventActivities.DateEnd >= '2014-10-06 10:00:00' AND EventActivities.DateEnd <= '2014-10-06 11:00:00'))
ORDER BY Alias

DDL

CREATE TABLE EventActivities (
  EventActivityId int(11) NOT NULL AUTO_INCREMENT,
  EventId int(11) NOT NULL,
  DateStart datetime NOT NULL,
  DateEnd datetime NOT NULL,
  CreatedOn datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
  CreatedBy int(11) NOT NULL,
  UpdatedOn datetime DEFAULT NULL,
  UpdatedBy int(11) DEFAULT NULL,
  FranchiseActivityId int(11) DEFAULT NULL,
  FranchisePackageId int(11) DEFAULT NULL,
  HourlyRate decimal(6, 2) DEFAULT NULL,
  HourlyRatePackage decimal(6, 2) DEFAULT NULL,
  HoursScheduled decimal(6, 2) DEFAULT NULL,
  PRIMARY KEY (EventActivityId),
  CONSTRAINT FK_EventActivities_Events_EventId FOREIGN KEY (EventId)
  REFERENCES Events (EventId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EventActivities_FranchiseActivities_FranchiseActivityId FOREIGN KEY (FranchiseActivityId)
  REFERENCES FranchiseActivities (FranchiseActivityId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EventActivities_FranchisePackages_FranchisePackageId FOREIGN KEY (FranchisePackageId)
  REFERENCES FranchisePackages (FranchisePackageId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EventActivities_Users_UserId FOREIGN KEY (CreatedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EventActivitiesUpd_Users_UserId FOREIGN KEY (UpdatedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 5
AVG_ROW_LENGTH = 4096
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE EventEquipment (
  EventActivityId int(11) NOT NULL,
  FranchiseEquipmentId int(11) NOT NULL,
  CONSTRAINT FK_EventEquipment_EventActivities_EventActivityId FOREIGN KEY (EventActivityId)
  REFERENCES EventActivities (EventActivityId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EventEquipment_FranchiseEquipment_FranchiseEquipmentId FOREIGN KEY (FranchiseEquipmentId)
  REFERENCES FranchiseEquipment (FranchiseEquipmentId) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AVG_ROW_LENGTH = 4096
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE FranchiseEquipment (
  FranchiseEquipmentId int(11) NOT NULL AUTO_INCREMENT,
  EquipmentMasterId int(11) NOT NULL,
  FranchiseId int(11) NOT NULL,
  Alias varchar(100) DEFAULT NULL,
  IsActive tinyint(4) NOT NULL DEFAULT 1,
  IsSuspended tinyint(4) NOT NULL DEFAULT 0,
  CreatedOn datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
  CreatedBy int(11) NOT NULL,
  UpdatedOn datetime DEFAULT NULL,
  UpdatedBy int(11) DEFAULT NULL,
  DeletedOn datetime DEFAULT NULL,
  DeletedBy int(11) DEFAULT NULL,
  PRIMARY KEY (FranchiseEquipmentId),
  CONSTRAINT FK_Equipment_EquipmentMaster_EquipmentMasterId FOREIGN KEY (EquipmentMasterId)
  REFERENCES EquipmentMaster (EquipmentMasterId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_Equipment_Franchises_FranchiseId FOREIGN KEY (FranchiseId)
  REFERENCES Franchises (FranchiseId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_Equipment_Users_UserId FOREIGN KEY (CreatedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EquipmentDel_Users_UserId FOREIGN KEY (DeletedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_EquipmentUpd_Users_UserId FOREIGN KEY (UpdatedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 8
AVG_ROW_LENGTH = 2730
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE FranchiseActivities (
  FranchiseActivityId int(11) NOT NULL AUTO_INCREMENT,
  MasterActivityId int(11) NOT NULL,
  FranchiseId int(11) NOT NULL,
  IsActive tinyint(4) NOT NULL DEFAULT 1,
  UpdatedOn datetime DEFAULT NULL,
  UpdatedBy int(11) DEFAULT NULL,
  DeletedOn datetime DEFAULT NULL,
  DeletedBy int(11) DEFAULT NULL,
  HourlyRate1 decimal(6, 2) NOT NULL DEFAULT 0.00,
  HourlyRate2 decimal(6, 2) NOT NULL DEFAULT 0.00,
  HourlyRate3 decimal(6, 2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (FranchiseActivityId),
  CONSTRAINT FK_FranActivities_Franchises_FranchiseId FOREIGN KEY (FranchiseId)
  REFERENCES Franchises (FranchiseId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_FranActivitiesDel_Users_UserId FOREIGN KEY (DeletedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_FranActivitiesUpd_Users_UserId FOREIGN KEY (UpdatedBy)
  REFERENCES Users (UserId) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_FranchiseActivities_ActivityMaster_ActivityMasterId FOREIGN KEY (MasterActivityId)
  REFERENCES ActivityMaster (ActivityMasterId) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
AUTO_INCREMENT = 6
AVG_ROW_LENGTH = 3276
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

CREATE TABLE ActivityMaster (
  ActivityMasterId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(75) NOT NULL,
  IsActive tinyint(4) NOT NULL DEFAULT 0,
  CreatedOn datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP',
  CreatedBy int(11) NOT NULL,
  UpdatedOn datetime DEFAULT NULL,
  UpdatedBy int(11) DEFAULT NULL,
  PRIMARY KEY (ActivityMasterId)
)
ENGINE = INNODB
AUTO_INCREMENT = 6
AVG_ROW_LENGTH = 4096
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

score 0 · Answer 1 · answered Sep 18 '14 at 14:14

Ok, I think I got this figured out from this site. Is a given booking period available?

My new query is as follows

SELECT
  EventActivities.DateStart,
  EventActivities.DateEnd,
  EventActivities.EventActivityId,
  EventEquipment.FranchiseEquipmentId,
  EventActivities.EventId,
  FranchiseEquipment.Alias,
  'Busy' AS Status,
  FranchiseActivities.HourlyRate1,
  ActivityMaster.Name AS Activity
FROM EventActivities
  INNER JOIN EventEquipment
    ON EventEquipment.EventActivityId = EventActivities.EventActivityId
  INNER JOIN FranchiseEquipment
    ON EventEquipment.FranchiseEquipmentId = FranchiseEquipment.FranchiseEquipmentId
  INNER JOIN FranchiseActivities
    ON EventActivities.FranchiseActivityId = FranchiseActivities.FranchiseActivityId
  INNER JOIN ActivityMaster
    ON FranchiseActivities.MasterActivityId = ActivityMaster.ActivityMasterId
WHERE FranchiseEquipment.FranchiseId = 1
AND EventEquipment.FranchiseEquipmentId IN (2,4,7)
AND 
  (EventActivities.DateStart < '2014-10-06 17:00:00' AND EventActivities.DateEnd > '2014-10-06 09:00:00')


UNION

SELECT
  NULL,
  NULL AS expr1,
  NULL AS expr2,
  FranchiseEquipment.FranchiseEquipmentId,
  NULL AS expr3,
  FranchiseEquipment.Alias,
  'Free' AS Status,
  FranchiseActivities.HourlyRate1,
  ActivityMaster.Name AS Activity
FROM FranchiseActivities_FranchiseEquipment
  INNER JOIN FranchiseEquipment
    ON FranchiseActivities_FranchiseEquipment.FranchiseEquipmentId = FranchiseEquipment.FranchiseEquipmentId
  INNER JOIN FranchiseActivities
    ON FranchiseActivities_FranchiseEquipment.FranchiseActivityId = FranchiseActivities.FranchiseActivityId
  INNER JOIN ActivityMaster
    ON FranchiseActivities.MasterActivityId = ActivityMaster.ActivityMasterId
WHERE FranchiseEquipment.FranchiseId = 1
AND FranchiseEquipment.FranchiseEquipmentId IN (2,4,7)
AND FranchiseEquipment.FranchiseEquipmentId NOT IN (SELECT
    EventEquipment.FranchiseEquipmentId
  FROM EventActivities
    INNER JOIN EventEquipment
      ON EventEquipment.EventActivityId = EventActivities.EventActivityId
  WHERE EventEquipment.FranchiseEquipmentId IN (2,4,7)
  AND   (EventActivities.DateStart < '2014-10-06 17:00:00' AND EventActivities.DateEnd > '2014-10-06 09:00:00'))
ORDER BY Alias

Retrieve data based on 2 datetime columns - reservation type system

1 Answers1