SQL Query for getting available locations within 2 dates in Access SQL

[Davor Geci]

I have a table with locations and the dates when they are occupied in Access mdb database.

CREATE TABLE `locations` (
`LocationID` int(11) NOT NULL,
`Location` varchar(255) NOT NULL,
`StartDate` date NOT NULL,
`EndDate` date NULL DEFAULT NULL
)


INSERT INTO `locations` VALUES (1, 'Venue A', '2023-10-01', '2023-10-10');
INSERT INTO `locations` VALUES (2, 'Venue B', '2023-10-15', '2023-10-20');
INSERT INTO `locations` VALUES (3, 'Venue C', '2023-10-05', '2023-10-12');
INSERT INTO `locations` VALUES (4, 'Venue D', '2023-10-08', NULL);
INSERT INTO `locations` VALUES (5, 'Venue E', '2023-10-25', NULL);
INSERT INTO `locations` VALUES (6, 'Venue F', '2023-10-21', '2023-10-25');
INSERT INTO `locations` VALUES (7, 'Venue G', '2023-10-30', '2023-11-05');
INSERT INTO `locations` VALUES (8, 'Venue H', '2023-11-01', '2023-11-10');
INSERT INTO `locations` VALUES (9, 'Venue I', '2023-11-15', NULL);
INSERT INTO `locations` VALUES (10, 'Venue J', '2023-12-01', '2023-12-05');
INSERT INTO `locations` VALUES (11, 'Venue K', '2023-12-10', '2023-12-20');
INSERT INTO `locations` VALUES (12, 'Venue L', '2023-12-15', NULL);

What would be the query to get locations that are free between 2 dates. For example, from '2023-10-21' and '2023-11-20' the result would be:

Location
Venue A
Venue B
Venue C
Venue J
Venue K
Venue L

[Zvoni]

Tested in SQLite

SELECT LocationID, Location FROM 
(SELECT LocationID, Location, StartDate, EndDate,
CASE WHEN StartDate>'2023-11-20' THEN True ELSE False
    END Or
CASE WHEN Coalesce(EndDate, '2039-12-31')<'2023-10-21' THEN True ELSE False END As MyCheck
FROM locations) 
WHERE MyCheck=True

Returns

LocationID	Location
1	Venue A
2	Venue B
3	Venue C
10	Venue J
11	Venue K
12	Venue L

EndDate = NULL means "Open End"?
It's the reason i used the Coalesce, moving it into a ridiculous future

Looks like MySQL. which Version?
There might be another Approach to skin that cat

Instead of the hardcoded Dates 2023-10-21 and 2023-11-20 you can use parameters

[Davor Geci]

Hello Zvoni,
thanks for your reply.
I'm testing this in MariaDB 10.4.24 but I need it for a project that is using old Access mdb database.

I try to run your query on MariaDB, it should work, but don't know why I'm getting error:
"1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE MyCheck=True' at line 7"

When I use only the internal part of the SQL:

SELECT LocationID, Location, StartDate, EndDate,
CASE WHEN StartDate>'2023-11-20' THEN True ELSE False
    END Or
CASE WHEN Coalesce(EndDate, '2039-12-31')<'2023-10-21' THEN True ELSE False END As MyCheck
FROM locations

I get the result:

LocationID	Location	StartDate	EndDate	MyCheck
1	Venue A	2023-10-01	2023-10-10	1
2	Venue B	2023-10-15	2023-10-20	1
3	Venue C	2023-10-05	2023-10-12	1
4	Venue D	2023-10-08		0
5	Venue E	2023-10-25		0
6	Venue F	2023-10-21	2023-10-25	0
7	Venue G	2023-10-30	2023-11-05	0
8	Venue H	2023-11-01	2023-11-10	0
9	Venue I	2023-11-15		0
10	Venue J	2023-12-01	2023-12-05	1
11	Venue K	2023-12-10	2023-12-20	1
12	Venue L	2023-12-15		1

[Zvoni]

"1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE MyCheck=True' at line 7"

Change to ".... WHERE MyCheck=1"

True/False gets "translated" to 1/0 (Integer)
Changed True/False to /1/0

SELECT LocationID, Location FROM 
(SELECT LocationID, Location, StartDate, EndDate,
CASE WHEN StartDate>'2023-11-20' THEN 1 ELSE 0
    END Or
CASE WHEN Coalesce(EndDate, '2039-12-31')<'2023-10-21' THEN 1 ELSE 0 END As MyCheck
FROM locations) 
WHERE MyCheck=1

EDIT: For Queries against an Access-mdb, you'll have to change the Coalesce to "...IIf(EndDate is Null, #2039-12-31#, EndDate)..."
The same for the CASE WHEN

IIf(StartDate>#2023-11-20#, 1, 0) Or 
IIf(IIf(EndDate is Null, #2039-12-31#, EndDate)<#2023-10-21#, 1, 0) As MyCheck