#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    10
    Rep Power
    0

    Question Not Exist query including a row that shouldn't be there.


    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
    )
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,789
    Rep Power
    9646
    What happens if you run the subquery manually?
    Code:
    SELECT * FROM dbo.wce_activity_temp AS a WHERE 'j1o3cjf2mj5ln5ni' = 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' ))
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    10
    Rep Power
    0
    Hi, thanks for looking. That returns no results but 4 out of the 5 results should be there as they do not have a start or end date that conflicts with the following booking requested.

    So the new booking is looking for a free property from the 17th Feb 2018 to 23rd Feb 2018. Here is the sample data. 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
    )
  6. #4
  7. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,789
    Rep Power
    9646
    Ha. It took me a couple more timess reading through your post to understand where the problem is: you think that the listing should be excluded because the scheduling exists in that window, however the query was returning it.

    Look at the times:
    - Schedule is Feb 17 2pm through Mar 15 9am
    - Query is looking for the times Feb 18 4pm through Feb 23 9am

    Now look at your subquery:
    - Schedule start time (Feb 17 2pm) must be between Feb 18 4pm and Feb 23 9am - it is not
    - Schedule end time (Mar 15 9am) must be between Feb 18 4pm and Feb 23 9am - it is not

    Therefore the subquery won't find the record, the NOT EXISTS will fail, and you'll get the property.

    Spelled out like that the mistake should be obvious: the logic the subquery is using is flawed.

    Looking at the times like it does now makes sense but it misses out on an important edge case where the schedule time wholly includes the desired time. That is, when the scheduled start is earlier than the desired start and the scheduled end is later than the desired end.

    Before you go adding that possibility in and making the subquery even more complicated, there's a better way to look for overlapping times.
    Code:
    a.startTime < '2018-02-23T09:00:00' AND a.endTime > '2018-02-18T16:00:00'
    Note that compares the scheduled start with the desired end, and the scheduled end with the desired start. I also switched to non-equality because I wouldn't think that one booking starting when the previous ends isn't really a conflict.

    Comments on this post

    • sward1378 agrees : Thank you, when explained in simple terms it makes perfect sense!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2006
    Posts
    10
    Rep Power
    0
    Thank you Requinix,

    When explained in simple terms it makes perfect sense! I think i have worked this from so many variations of different query logics my head just got overwhelmed! Thanks again, looks good upon my first few tests.

IMN logo majestic logo threadwatch logo seochat tools logo