Hi, excuse me if this is a little hard to follow.
I have a table that stores bookings for properties called wce_activity_temp, this holds the property name and the start and end dates etc. I have another table that stores the properties names and some other bits realted to the properties called wces_users.
I have a script that when run i want to check the properties booked in the wce_activity_temp table and to only return those properties that are not booked for the given start and end date in the NOT EXISTS query below, just returning the properties from wces_users that are still available.
This query is the one that i believe should avoid displaying propname 151 LS - 3BD APT (JV)
SELECT U.uniqueid, U.WCE_UID as propname, wce_DisplayName FROM wces_users AS u
WHERE u.WCE_DEPARTMENT = 'Accommodation' AND NOT EXISTS (SELECT a.SCHEDULEFOR FROM dbo.wce_activity_temp AS a WHERE u.UNIQUEID = a.SCHEDULEFOR AND ((a.startTime <= '2018-02-23T09:00:00' AND a.startTime >= '2018-02-18T16:00:00' ) OR (a.endTime <= '2018-02-23T09:00:00' AND a.endTime >= '2018-02-18T16:00:00' ))) order by uniqueidSo the results of the above query should exclude uniqueid 1, propname 151 LS - 3BD APT (JV) in my example below but for some reason the row is still displayed. There is a STARTTIME of 2018-02-17 14:00:00.000 and end time 2018-03-15 09:00:00.000 which conflict with my not exists query.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT wce_activity_temp.[UNIQUEID]
,[SCHEDULEFOR]
,wce_uid as propname
,[STARTTIME]
,[EndTime]
FROM [wce_activity_temp] inner join wces_users on wce_activity_temp.schedulefor = wces_users.uniqueid
My head has gone dizzy looking at this and i am guessing i am nearly there but missing something simply. Just need another pair of eyes to educate me.
Below is the script to create the sample tables / data.
Thanks for looking, i will watch out for any questions.
create database test_db
Go
USE test_db
CREATE TABLE [dbo].[wce_activity_temp](
[UNIQUEID] [varchar](16) NOT NULL,
[STARTTIME] [datetime] NULL,
[SCHEDULEFOR] [varchar](16) NULL,
[EndTime] [datetime] NULL)
insert into wce_activity_temp (UNIQUEID, STARTTIME, SCHEDULEFOR, EndTime) values ('1','2018-02-17 14:00:00.000','j1o3cjf2mj5ln5ni','2018-03-15 09:00:00.000')
insert into wce_activity_temp (UNIQUEID, STARTTIME, SCHEDULEFOR, EndTime) values ('2','2018-04-17 14:00:00.000','njf3bj33sni1ogvg','2018-05-15 09:00:00.000')
insert into wce_activity_temp (UNIQUEID, STARTTIME, SCHEDULEFOR, EndTime) values ('3','2018-06-17 14:00:00.000','njf3k8h20bb39g77','2018-07-15 09:00:00.000')
insert into wce_activity_temp (UNIQUEID, STARTTIME, SCHEDULEFOR, EndTime) values ('4','2018-08-17 14:00:00.000','j1o3cjf2mj5ln5ni','2018-09-15 09:00:00.000')
insert into wce_activity_temp (UNIQUEID, STARTTIME, SCHEDULEFOR, EndTime) values ('5','2018-10-17 14:00:00.000','njf3k8h20bb39g77','2018-11-15 09:00:00.000')
CREATE TABLE [dbo].[wces_users](
[UNIQUEID] [varchar](16) NOT NULL,
[WCE_UID] [varchar](90) NULL,
[WCE_DISPLAYNAME] [varchar](90) NULL,
[WCE_DEPARTMENT] [varchar](30) NULL )
insert into wces_users (UNIQUEID, WCE_UID, WCE_DISPLAYNAME, WCE_DEPARTMENT)
values ('j1o3cjf2mj5ln5ni','151 LS - 3BD APT (JV)','151 LS - 3BD APT','Accommodation')
insert into wces_users (UNIQUEID, WCE_UID, WCE_DISPLAYNAME, WCE_DEPARTMENT)
values ('njf3k8h20bb39g77','10 LF - 2BD DUP (JV)','10 LF - 2BD DUP','Accommodation')
insert into wces_users (UNIQUEID, WCE_UID, WCE_DISPLAYNAME, WCE_DEPARTMENT)
values ('njf3xrg2gtgdpb9a','121 LS - 2BD DUP','121 LS - 2BD DUP','Accommodation')
insert into wces_users (UNIQUEID, WCE_UID, WCE_DISPLAYNAME, WCE_DEPARTMENT)
values ('njf3bj33sni1ogvg','11 LR - 2BD CMV (JV)','11 LR - 2BD CMV','Accommodation')
GO
)