SQL Query Issue

mshsilver · November 20, 2016, 4:25pm

Hi,

I have two tables wces_users_copy which holds a list of properties amongst other things and wce_activity_copy a list of properties booked with a start time and end time.

I am using this following query to show all the properties that DO NOT have a booking between a specific starttime and endtime:

SELECT U.WCE_UID as propname, U.uniqueid as schedforuniqueid, uniqueid 
FROM wce_sysusers_copy AS u WHERE u.WCE_DEPARTMENT = 'Accommodation' AND NOT EXISTS 
(SELECT a.SCHEDULEFOR FROM dbo.wce_activity_copy AS a 
WHERE u.UNIQUEID = a.SCHEDULEFOR AND a.STARTTIME > '2016-12-06T16:00:00' AND a.EndTime < '2016-12-11T10:00:00')

I thought this query was working great but I have found a record that is being returned that shouldn’t appear based on the above query, or so I thought.

This row:

propname -------- schedforuniqueid -------- uniqueid
133 LS - 1/2BD APT -------- ljf3mfb4l2k1ac49 -------- ljf3mfb4l2k1ac49

You will see that there is a booking in the wce_activity_code table if you run this code.

select schedulefor, STARTTIME, EndTime from wce_activity_copy
WHERE schedulefor = 'ljf3mfb4l2k1ac49' and starttime > '2016/12/10' and endtime < '2016/12/18' 
order by STARTTIME desc

This returns:

schedulefor -------- STARTTIME -------- EndTime
ljf3mfb4l2k1ac49 -------- 2016-12-10 16:00:00.000 -------- 2016-12-17 10:00:00.000

Below are the two tables in question along with the sample data. It would be great if someone can tell me what I have got wrong here.

If you have any questions please let me know. Many thanks.

USE [test db]
GO
/****** Object:  Table [dbo].[wce_sysusers_copy]    Script Date: 11/20/2016 16:29:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wce_sysusers_copy](
	[UNIQUEID] [varchar](16) NOT NULL,
	[WCE_UID] [varchar](30) NULL,
	[WCE_DISPLAYNAME] [varchar](30) NULL,
	[WCE_ALIAS] [varchar](30) NULL,
	[WCE_DEPARTMENT] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'', N'Copy of 20 LF II - 2BD DUP (JV', N'Copy of 20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf32hm2pzechitf', N'12 PH - 4BD IV (JV)', N'12 PH - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf339l2z2zc8fxe', N'14 PH - 4BD IV (JV)', N'14 PH - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf33jz18uj1sze6', N'3 ER - 3BD CMV (JV)', N'3 ER - 3BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf35o82rrt3nyl1', N'4 DG - 4BD CMV (JV)', N'4 DG - 4BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf367820ztaiq71', N'8 LR - 4BD CMV (JV)', N'8 LR - 4BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3bjf2yzlhhd6i', N'5 PH - 3BD IV (JV)', N'5 PH - 3BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3c5l2vg9a6ppe', N'9 BV - 4BD IV (JV)', N'9 BV - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3clg2rlo80a2s', N'5 SWID - 3BD IV (JV)', N'5 SWID - 3BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3h8h23we2pshc', N'15 PH - 3BD IV (JV)', N'15 PH - 3BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3k0l2cckf1lld', N'9 PH - 4BD IV (JV)', N'9 PH - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3kgh2ooyd6jl2', N'4 SD - 4BD IV (JV)', N'4 SD - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3mf82wzq7frel', N'2 LE - 4BD CMV', N'2 LE - 4BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3n062pgbbqtac', N'10 LR - 3BD CMV (JV)', N'10 LR - 3BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3ns52slb6009f', N'10 DG - 3BD CMV (JV)', N'10 DG - 3BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3o7l2phgfcikc', N'29 DD - 4BD IV (JV)', N'29 DD - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3pbl24jufxf5b', N'28 DD - HER (JV)', N'28 DD - HER', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3quf274ojt4b9', N'6 BV - 3BD IV (JV)', N'6 BV - 3BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3rcg2nekdqutg', N'24 DD - 3BD IV (JV)', N'24 DD - 3BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3t8z1lvk3qz93', N'1 LE - 3BD CMV (JV)', N'1 LE - 3BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3ttk20b3awoi8', N'10 DD - 4BD IV', N'10 DD - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3twk297clhzxi', N'16 PH - 4BD IV (JV)', N'16 PH - 4BD IV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'0kf3un721u3lhq57', N'14 LE - 4BD CMV (JV)', N'14 LE - 4BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'20l2tgh225646ne6', N'Adela Rubio', N'Z1', N'Almanzora_Site', N'CM')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'2g83rk037ye69enh', N'9 DD - 3BDIV (JV)', N'9 DD - 3BDIV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'3f0339y3d77lrs4k', N'Neil Evans', N'Z7', N'Almanzora_Site', N'Golf')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'4od2kmb3hp0eoh1k', N'pft', N'Paul Taylor', N'Almanzora_Site', N'Systems')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'brk2z3o3pn2bweob', N'pft1', N'Paul Taylor T1', N'Almanzora_Site', N'Systems')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'gjf3cs93o13d5o7l', N'Gloria Martinez', N'Gloria Martinez', N'Almanzora_Site', N'F & B')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'kjf38su2lhg1iyvi', N'112 LS - 2BD (JV)', N'112 LS - 2BD', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf30ya40kk4v1b4', N'131 LS - 2BD APT', N'131 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf38cb4pau5uh8k', N'136 LS - 2BD APT', N'136 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf39vb40s8b8eae', N'213 LS - 2BD APT', N'213 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3hcc49rgj758k', N'215 LS - 1/2BD (JV)', N'215 LS - 1/2BD', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3m9b48iy8ug9l', N'214 LS - 2BD APT (JV)', N'214 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3mfb4l2k1ac49', N'133 LS - 1/2BD APT', N'133 LS - 1/2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3n3c4v57k0mnq', N'233 LS - 1/2BD APT', N'233 LS - 1/2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3nib4fyu1p78a', N'114 LS - 1/2BD APT', N'114 LS - 1/2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3q844iv522yv6', N'331 LS - 2BD APT (JV)', N'331 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3vta4qupkw1m6', N'330 LS - 2BD APT', N'330 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3z0b40nmj6tue', N'132 LS - 2BD APT (JV)', N'132 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3zka40mqhbxib', N'332 LS - 2BD APT', N'332 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'ljf3zqa4iaed2wm7', N'333 LS - 2BD APT', N'333 LS - 2BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'mh03zy138zs8m1c8', N'Copy of FBQS', N'Copy of Food & Beverage DE', N'Almanzora_Site', N'F & B')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'mqd2vh82tggavpm8', N'Jo Kerr', N'Jo Kerr', N'Almanzora_Site', N'F & B')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf32mg2g3x3kiof', N'Copy of 19 LF II - 2BD DUP (JV', N'Copy of 19 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf32pk2pmbcopdb', N'140 LS - 2BD DUP (JV)', N'140 LS - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf32yk2jdg31hxj', N'Copy of 19 LF II - 2BD DUP (JV', N'Copy of 19 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf36813r69i0e9k', N'248 LS - 3BD APT (JV)', N'248 LS - 3BD APT', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf395h2o6q5nvug', N'111 LS - 2BD DUP (JV)', N'111 LS - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf39og2bo340ckv', N'Copy of 20 LF II - 2BD DUP (JV', N'Copy of 20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3b5g2fxlgmpvc', N'Copy of 20 LF II - 2BD DUP (JV', N'Copy of 20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3bj33sni1ogvg', N'11 LR - 2BD CMV (JV)', N'11 LR - 2BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3fgh2n0773aue', N'25 LF - 2BD DUP ', N'25 LF - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3fok21u73tixb', N'Copy of 19 LF II - 2BD DUP (JV', N'Copy of 19 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3k8h20bb39g77', N'10 LF - 2BD DUP (JV)', N'10 LF - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3l2g2tlj3ghr3', N'Copy of 20 LF II - 2BD DUP (JV', N'Copy of 20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3llk2o12hg19d', N'23 LF - 2BD DUP (JV)', N'23 LF - 2BD DUP ', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3ocg2yipfhhbc', N'Copy of 19 LF II - 2BD DUP (JV', N'Copy of 19 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3pt13hb7bkxqg', N'105 LS - 3BD DUP (JV)', N'105 LS - 3BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3qq13l4xg3lgj', N'1 LF - 3BD DUP', N'1 LF - 3BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3qw13c8w9gv2l', N'144 LS - 3BD DUP (JV)', N'144 LS - 3BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3r4435j7jyc8a', N'6 DG - 3BD CMV (JV)', N'6 DG - 3BD CMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3rng20f7i58v1', N'Copy of 20 LF II - 2BD DUP (JV', N'Copy of 20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3ruk29r8c0kj2', N'6 LF - 2BD DUP (JV)', N'6 LF - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3xrg2gtgdpb9a', N'121 LS - 2BD DUP', N'121 LS - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3y7g2kqfhonfe', N'19 LF II - 2BD DUP (JV)', N'19 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'njf3ydh2hzk7aaf1', N'5 LF - 2BD DUP', N'5 LF - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'pbi1dbg29yp8lmme', N'Paul Gutierrez', N'Paul Gutierrez', N'Almanzora_Site', N'CM')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'pbi1dbg2ohc29pah', N'Elvira Casas', N'Z2', N'Almanzora_Site', N'CM')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'pbi1ebg2p8c2g2t9', N'QP JB Clear', N'X1', N'Almanzora_Site', N'CM')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'pqd27f82tl41xpgd', N'FBQS', N'Food & Beverage DE', N'Almanzora_Site', N'F & B')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'uqd25qj1nfliztri', N'pftholiday', N'Paul Taylor', N'Almanzora_Site', N'Systems')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'v6d2zku29c271h14', N'Paul Taylor', N'Paul Taylor', N'Almanzora_Site', N'Systems')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'zf836no2ol770o0k', N'20 LF II - 2BD DUP (JV)', N'20 LF II - 2BD DUP', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'zf83iuo2uts1bb6c', N'11 LR - 2BCMV', N'11 LR - 2BCMV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'zf83ivo258bixiyf', N'6 DG - 3BCV', N'6 DG - 3BCV', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'zf83pso2liefof9g', N'105 LS - 3BD', N'105 LS - 3BD', N'Almanzora_Site', N'Accommodation')
INSERT [dbo].[wce_sysusers_copy] ([UNIQUEID], [WCE_UID], [WCE_DISPLAYNAME], [WCE_ALIAS], [WCE_DEPARTMENT]) VALUES (N'zf83qro22yp8ycp7', N'248 LS - 3BA', N'248 LS - 3BA', N'Almanzora_Site', N'Accommodation')
/****** Object:  Table [dbo].[wce_activity_copy]    Script Date: 11/20/2016 16:29:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wce_activity_copy](
	[UNIQUEID] [varchar](16) NOT NULL,
	[CREATETIME] [datetime] NULL,
	[EDITTIME] [datetime] NULL,
	[ATYPE] [varchar](175) NULL,
	[APRIORITY] [varchar](12) NULL,
	[SUBJECT] [varchar](70) NULL,
	[STARTTIME] [datetime] NULL,
	[DURATION] [int] NULL,
	[TIMELESS] [varchar](1) NULL,
	[LEADTIME] [int] NULL,
	[ALARMSTATUS] [varchar](1) NULL,
	[COLOR] [varchar](20) NULL,
	[SCHEDULEFOR] [varchar](16) NULL,
	[CREATEUSER] [varchar](16) NULL,
	[CLEARSTATUS] [int] NULL,
	[NOTES] [text] NULL,
	[EDITUSER] [varchar](16) NULL,
	[MANAGINGTEAM] [varchar](25) NULL,
	[ALARMTIME] [datetime] NULL,
	[StopTime] [datetime] NULL,
	[EndTime] [datetime] NULL,
	[Quote_No] [int] NULL,
	[Service_Property_UID] [varchar](16) NULL,
	[Assigned_To] [varchar](50) NULL,
	[Next_Action] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'fxf3zm729tzhjick', CAST(0x0000A62200ADA264 AS DateTime), CAST(0x0000A62200ADA264 AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D80107AC00 AS DateTime), 10080, NULL, NULL, NULL, NULL, N'njf3y7g2kqfhonfe', N'do831xm28g69s91c', NULL, NULL, N'do831xm28g69s91c', NULL, NULL, NULL, CAST(0x0000A6DF00A4CB80 AS DateTime), 878, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'm8g30x733y1a05pg', CAST(0x0000A69F00F7DEB0 AS DateTime), CAST(0x0000A69F00F7DEB0 AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D60107AC00 AS DateTime), 2880, NULL, NULL, NULL, NULL, N'kjf38su2lhg1iyvi', N'do831xm28g69s91c', NULL, NULL, N'do831xm28g69s91c', NULL, NULL, NULL, CAST(0x0000A6D800A4CB80 AS DateTime), 1331, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'mpf3rho209tddx79', CAST(0x0000A5D900D42560 AS DateTime), CAST(0x0000A5D900D481F4 AS DateTime), N'Booking', N'300', N'booking', CAST(0x0000A6D500B16DE0 AS DateTime), 5, N'0', 10, N'0', NULL, N'do831xm28g69s91c', N'do831xm28g69s91c', NULL, N'el año pasado reservo el 28/05-04/06 resa 494', N'do831xm28g69s91c', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N't8g3sp52z3225ha4', CAST(0x0000A6A600A6CDF4 AS DateTime), CAST(0x0000A6A600A6D04C AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D30107AC00 AS DateTime), 10080, NULL, NULL, NULL, NULL, N'njf3llk2o12hg19d', N'hps2vum2gkqkv1ak', NULL, NULL, N'hps2vum2gkqkv1ak', NULL, NULL, NULL, CAST(0x0000A6DA00A4CB80 AS DateTime), 1348, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'uuf37l03ge13lgx1', CAST(0x0000A60A00E8C704 AS DateTime), CAST(0x0000A60A00E8C704 AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D80107AC00 AS DateTime), 10080, NULL, NULL, NULL, NULL, N'ljf3mfb4l2k1ac49', N'hps2vum2gkqkv1ak', NULL, NULL, N'hps2vum2gkqkv1ak', NULL, NULL, NULL, CAST(0x0000A6DF00A4CB80 AS DateTime), 897, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'vxf3itf2qiaa2uda', CAST(0x0000A63200BFC688 AS DateTime), CAST(0x0000A63200BFC688 AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D80107AC00 AS DateTime), 10080, NULL, NULL, NULL, NULL, N'zf836no2ol770o0k', N'do831xm28g69s91c', NULL, NULL, N'do831xm28g69s91c', NULL, NULL, NULL, CAST(0x0000A6DF00A4CB80 AS DateTime), 878, NULL, NULL, NULL)
INSERT [dbo].[wce_activity_copy] ([UNIQUEID], [CREATETIME], [EDITTIME], [ATYPE], [APRIORITY], [SUBJECT], [STARTTIME], [DURATION], [TIMELESS], [LEADTIME], [ALARMSTATUS], [COLOR], [SCHEDULEFOR], [CREATEUSER], [CLEARSTATUS], [NOTES], [EDITUSER], [MANAGINGTEAM], [ALARMTIME], [StopTime], [EndTime], [Quote_No], [Service_Property_UID], [Assigned_To], [Next_Action]) VALUES (N'w8g3ua33yr7e69ld', CAST(0x0000A6A900F27588 AS DateTime), CAST(0x0000A6A900F27588 AS DateTime), N'Booking', NULL, NULL, CAST(0x0000A6D50107AC00 AS DateTime), 5760, NULL, NULL, NULL, NULL, N'njf3bj33sni1ogvg', N'e3t2tf92y0uakjv4', NULL, NULL, N'e3t2tf92y0uakjv4', NULL, NULL, NULL, CAST(0x0000A6D900A4CB80 AS DateTime), 1360, NULL, NULL, NULL)

stepson · November 20, 2016, 4:50pm

The idea is to have the startTime between @dateStart and @dateEnd
or have the endTime between @dateStart and @dateEnd.

I believe this is more complete:

SELECT U.WCE_UID as propname, U.uniqueid as schedforuniqueid, uniqueid 
FROM wce_sysusers_copy AS u 
WHERE u.WCE_DEPARTMENT = 'Accommodation' 
AND NOT EXISTS 
(SELECT a.SCHEDULEFOR FROM dbo.wce_activity_copy AS a 
WHERE u.UNIQUEID = a.SCHEDULEFOR 
	AND (
		(a.startTime < '2016-12-11T10:00:00' AND a.startTime > '2016-12-06T16:00:00' )
		OR 
		(a.endTime < '2016-12-11T10:00:00' AND a.endTime > '2016-12-06T16:00:00' )
		)
)

One question is raising : should your interval @dateStart -@dateEnd be full contained in startTime -endTime ?

mshsilver · November 20, 2016, 6:08pm

Hi, thank you for your reply, that looks like it might work. I am testing your query now.

So the idea is that if there are any properties booked between the start and end time specified in the query then those properties should not appear. Is that what you mean when you say __'should your interval @dateStart -@dateEnd be full contained in startTime -endTime'_

Any advice greatly received. Thanks

stepson · November 20, 2016, 6:20pm

There is situation when a booked property end before upper limit. So there is a rest of time where that property is not booked. (not fully booked)

Test that query and see what happens .(and adjust it by adding extra conditions)

mshsilver · November 23, 2016, 4:55pm

Thank you Stepson, your query I think has resolved my issues. I will continue to test.

Thanks again.

stepson · November 24, 2016, 6:36am

You're welcome