×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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)

CODE

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.


CODE

/****** 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.


CODE

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
) 

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

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

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

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

(OP)
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.

[code]
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
)

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

(OP)
It look like my logic was wrong!

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

CODE

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.

CODE

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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close