SQLTeam.com | Weblogs | Forums

Not Exists Script Issue


#1

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
)

#2

When I get dizzy I get me some coffee and start working inside out meaning start with subqueries and test them by themselves

SELECT a.SCHEDULEFOR 
FROM #wce_activity_temp AS a 
WHERE ((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' ))

the query in NOT EXISTS brings back no rows. That might be your issue


#3

Sorry not sure what was wrong with that query.

This is it.


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

Here are the results of the query.

uniqueid propname wce_DisplayName
j1o3cjf2mj5ln5ni 151 LS - 3BD APT (JV) 151 LS - 3BD APT
njf3bj33sni1ogvg 11 LR - 2BD CMV (JV) 11 LR - 2BD CMV
njf3k8h20bb39g77 10 LF - 2BD DUP (JV) 10 LF - 2BD DUP
njf3xrg2gtgdpb9a 121 LS - 2BD DUP 121 LS - 2BD DUP