Not Exist Query including a row that should not be there.

[craigward]

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 uniqueid

So 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
)

 

[fredericofonseca]

your "not exists" is filtering on dates which do not cause the "not exists" to be true.

you inner join does not use the same dates hence it does find valid records.

your test query should have been

select a.UNIQUEID
     , a.SCHEDULEFOR
     , b.wce_uid as propname
     , a.STARTTIME
     , a.EndTime
from wce_activity_temp a
inner join wces_users b
    on a.schedulefor = b.uniqueid
    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'))

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[craigward]

Thanks for looking. Maybe this logic has my head confused.

Let me ask the question in a different way. As I understand the not exists query below should return 4 out of the 5 results as they do not have a start or end date that conflicts with the following booking requested.

So the new booking is looking for free properties from the 17th Feb 2018 to 23rd Feb 2018. Here is the sample data again. Thanks for your help.

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
)

 

[craigward]

It look like my logic was wrong!

My original query to avoid a property booked between two dates.

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 uniqueid

A revised version from another developer on a different site.

The starttime < end time of new booking and endtime > starttime of new booking.

Hope this helps someone else.

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.endTime > '2018-02-18T16:00:00' ))) order by uniqueid