Counting field values per day between specific date ranges

mshsilver · October 24, 2016, 3:20pm

Hi All, thanks in advance for any help.

I have a table where I store holiday bookings, one row per booking. What I am trying to do is produce a sql view that breaks this data down per day and shows for every day for days that are booked (between the arrival_date and departure_date) how many adults and children are on holiday.

There is a field to capture the start_date and the end_date, also a field for the number of adults and another field for the number of children. There is of course the uniqueid for each row, the primary key.

Code and sample data are below to generate the scenario.

Here is an attempt that is kind of what I am looking for but this is not giving me the correct results. Any questions please let me know.

Thanks again.

SELECT     DATEADD(dd, 0, DATEDIFF(dd, 0, Arrival_Date)) AS date, SUM(No_Adults) AS Adults, SUM(No_Children) AS Children, DATENAME(dw, Arrival_Date) AS Day, DATENAME(month, Arrival_Date) AS Month, 
                      DATENAME(year, Arrival_Date) AS Year
FROM         dbo.wce_bookings
where '2013/01/01' < Arrival_Date AND '2055/01/01' > Departure_Date
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, Arrival_Date)), Arrival_Date

TABLE SCRIPT AND SAMPLE DATA.

USE [testdb]
GO
/****** Object:  Table [dbo].[wce_bookings]    Script Date: 10/24/2016 16:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wce_bookings](
	[UniqueID] [varchar](16) NOT NULL,
	[Quote_No] [int] NOT NULL,
	[No_Adults] [int] NULL,
	[No_Children] [int] NULL,
	[Arrival_Date] [datetime] NULL,
	[Departure_Date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

print 'Processed 100 total records'
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf37ko27gjf7tea', 552, 6, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A63E00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf37no3cwqghnlj', 593, 6, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf381928uub4jnk', 543, 8, 0, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf38d23sd8cab1f', 554, 6, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf38p34ef278n67', 604, 4, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A62D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf39h93s6fccgl5', 565, 4, 1, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf39um296tf0to1', 549, 2, 1, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3dgf2tffb3u9d', 545, 4, 2, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3efq3016jlyd8', 596, 4, 0, CAST(0x0000A6260107AC00 AS DateTime), CAST(0x0000A62A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3fx939mli1jf6', 566, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3g593oszj2tuk', 564, 2, 1, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3gwq30tu9oen6', 597, 2, 0, CAST(0x0000A6260107AC00 AS DateTime), CAST(0x0000A62D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3jpa3f4gcgqr9', 568, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3kdb319ngeu67', 570, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3kq334k452cq7', 557, 2, 2, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3lal2kvs7hukf', 546, 6, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mdn2i8z2szhd', 550, 2, 2, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63E00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mfj3m7s4bwnk', 588, 7, 0, CAST(0x0000A61E0107AC00 AS DateTime), CAST(0x0000A62500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mpo2r6e1rx15', 553, 2, 2, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3p5i3sl6i0iyl', 579, 3, 0, CAST(0x0000A61C0107AC00 AS DateTime), CAST(0x0000A62000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3q783bo2gg8ie', 561, 4, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3qg83aq3ecg6d', 562, 2, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3saz324hhih34', 602, 8, 0, CAST(0x0000A6280107AC00 AS DateTime), CAST(0x0000A62C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3u2b3tzm4lfi4', 569, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3vaa334njwpa4', 567, 6, 1, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3xi43yx3lyoe9', 560, 2, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3xin3icte341c', 591, 8, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y143xgjj9qie', 558, 3, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y243l6jdi2s3', 559, 2, 2, CAST(0x0000A6160107AC00 AS DateTime), CAST(0x0000A61D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y7o2tppjax8f', 551, 3, 2, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63700A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3yv23olkdtyqf', 555, 3, 0, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3z2v3197c6hy6', 601, 6, 0, CAST(0x0000A6270107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3gvg3g2dddgy4', 810, 0, 0, CAST(0x0000A61100F73140 AS DateTime), CAST(0x0000A61F00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3lk13py6fec6h', 805, 0, 0, CAST(0x0000A65300F73140 AS DateTime), CAST(0x0000A661009450C0 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3nja3rls8ge9f', 807, 0, 0, CAST(0x0000A62900F73140 AS DateTime), CAST(0x0000A63900735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3phf3tzdd8ol6', 809, 0, 0, CAST(0x0000A60300F73140 AS DateTime), CAST(0x0000A60D00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3q2931vm62rx5', 806, 0, 0, CAST(0x0000A62200F73140 AS DateTime), CAST(0x0000A62D0107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3rrb3qlvj8lt6', 808, 0, 0, CAST(0x0000A62A00F73140 AS DateTime), CAST(0x0000A636010FE960 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf351p3w74j1job', 959, 4, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3cq62ok98scif', 956, 3, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3izc2h1aiydth', 957, 2, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3uu52bfxazlzi', 955, 2, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3wrx10kok4sre', 952, 2, 2, CAST(0x0000A6400107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3x4435paild58', 958, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dsf3bqi3sfng1jx3', 816, 6, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dxf31hs2jzac6tch', 962, 2, 2, CAST(0x0000A6400107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dxf378m20cnpav7h', 961, 2, 2, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'e0g3bg23c2k10gvu', 1057, 3, 0, CAST(0x0000A64E0107AC00 AS DateTime), CAST(0x0000A64F00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'e0g3mhs2vp0lkwcj', 1056, 2, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'epf31r73f7v92osd', 612, 2, 0, CAST(0x0000A5F90107AC00 AS DateTime), CAST(0x0000A5FB00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'esf3sgo2s1h2x23a', 819, 2, 1, CAST(0x0000A6390107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf31hi3qqu2fnbj', 824, 2, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf321i3ak78we6b', 823, 1, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf34lj3xejb53pc', 826, 2, 0, CAST(0x0000A63F0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf36pg2tx1l37mj', 821, 2, 3, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63700A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf3cl92arnklpfi', 820, 4, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf3ybj3vbsjrael', 825, 2, 0, CAST(0x0000A63D0107AC00 AS DateTime), CAST(0x0000A64000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'f0g3y4033atkk3vg', 1058, 3, 0, CAST(0x0000A6480107AC00 AS DateTime), CAST(0x0000A64B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf34e028ohdql4i', 300, 6, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf3f7d20dqigdn5', 302, 4, 0, CAST(0x0000A5FB0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf3w562ow1jjlvg', 301, 0, 0, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A6530107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf34cy1ng0axk7j', 617, 8, 0, CAST(0x0000A62F0107AC00 AS DateTime), CAST(0x0000A63300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf385s1rs31i9gi', 614, 4, 0, CAST(0x0000A62D0107AC00 AS DateTime), CAST(0x0000A63100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf3i5u1q3min7o4', 615, 4, 0, CAST(0x0000A62D0107AC00 AS DateTime), CAST(0x0000A63100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf3krx1jzx2z5a1', 616, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf30ph3ahag6fcb', 841, 2, 2, CAST(0x0000A61400A4CB80 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf30xd2lwti9jie', 831, 0, 0, CAST(0x0000A63D00F73140 AS DateTime), CAST(0x0000A64300735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf37ag2dgbij1d7', 833, 2, 2, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3nhj340y8p411', 842, 2, 3, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3sdl2s9pg58ie', 835, 0, 0, CAST(0x0000A61100F73140 AS DateTime), CAST(0x0000A61F00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3wxj39wgibjkb', 843, 2, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3y8n2lb61o56i', 838, 6, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g31k82jv8eymc8', 1064, 0, 0, CAST(0x0000A6540107AC00 AS DateTime), CAST(0x0000A65800A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g3kvt20k6zjsb2', 1070, 2, 0, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g3opn203plhaq9', 1069, 2, 0, CAST(0x0000A6530107AC00 AS DateTime), CAST(0x0000A66000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h3g3z0h3r3oc2h14', 1142, 1, 0, CAST(0x0000A6360107AC00 AS DateTime), CAST(0x0000A67300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hpf38pm2yw2fnx8h', 622, 2, 0, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf38pl2eqaihibl', 845, 4, 3, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf3pd33cla2lt27', 847, 1, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf3wdg2m01d3lp3', 844, 4, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3bsn23l7ehhqa', 970, 3, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3ogm2zyzhyl8j', 969, 6, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3qi9201t1lw13', 968, 2, 0, CAST(0x0000A6470107AC00 AS DateTime), CAST(0x0000A65500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3r1g204a9aoo8', 1075, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ipf3x8t2qwo52f9l', 628, 4, 0, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65400A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'iuf3b4j3yu3esbvg', 851, 2, 0, CAST(0x0000A60E0107AC00 AS DateTime), CAST(0x0000A61200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ixf3bum2jc4huou4', 973, 4, 0, CAST(0x0000A64E0107AC00 AS DateTime), CAST(0x0000A65900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ixf3lc238ibc3pfc', 975, 4, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'jxf3zck4ie5ina1f', 977, 2, 1, CAST(0x0000A6530107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kjf39r33ru24mozi', 207, 2, 0, CAST(0x0000A60B0107AC00 AS DateTime), CAST(0x0000A61200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kpf32ts2jfzdy1bb', 630, 0, 0, CAST(0x0000A6130107AC00 AS DateTime), CAST(0x0000A61A0107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf342d4wwg6nz3j', 856, 4, 2, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf39fd4wl4drkle', 857, 7, 1, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf39vc43zed5f56', 855, 4, 2, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3cvb4rqt7hjxc', 853, 2, 1, CAST(0x0000A60D0107AC00 AS DateTime), CAST(0x0000A61400A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3lqd4472e1gtk', 858, 3, 0, CAST(0x0000A6040107AC00 AS DateTime), CAST(0x0000A60B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3n7i4eb5dum1i', 861, 4, 0, CAST(0x0000A6150107AC00 AS DateTime), CAST(0x0000A61C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3v4b4eoheqat8', 852, 2, 0, CAST(0x0000A5FF0107AC00 AS DateTime), CAST(0x0000A60600A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3xte4dib39xk6', 860, 4, 0, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3zic4kt157pma', 854, 4, 4, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kxf3lyf3wtx383pk', 979, 1, 0, CAST(0x0000A6000107AC00 AS DateTime), CAST(0x0000A63600A4CB80 AS DateTime))
GO

ScottPletcher · October 24, 2016, 5:22pm

I believe you may need a "standard" tally table to expand the date range into separate dates, so I added a cte in the code below to do that.

There is of course the uniqueid for each row, the primary key.

No! This table should be clustered first on arrival_date, not on a useless id, since that is the way you will query the table. You can make UniqueId the second key column to make the clus index unique. It does not necessarily need to be a PK, but can be one if you like. The single most damaging myth in table design is that "every" table should be clustered on an identity column: often terrible for overall performance.

WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0) AS date,
    SUM(No_Adults) AS Adult,
    DATENAME(YEAR, wb.Arrival_date) AS Year
FROM wce_bookings wb
INNER JOIN cteTally10K t ON 
    t.number BETWEEN 0 AND DATEDIFF(DAY, wb.Arrival_Date, wb.Departure_date)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0), DATENAME(YEAR, wb.Arrival_date)
ORDER BY date

mshsilver · November 3, 2016, 3:11pm

Hi Scott,

Hope you can maybe help some more with your example. I am trying to validate the results of your query based on a daily check on the sample table/data above but have some inconsistencies and I can't see why. This probably due to my lack of knowledge so any guidance would be really helpful. Thank you in advance.

Here is my query I have used to spot check days from the data sample above.


SELECT quote_no, Arrival_Date, departure_Date, no_adults, no_children FROM wce_bookings WHERE '20160725' BETWEEN Arrival_Date AND departure_Date
SELECT sum(No_Adults) FROM wce_bookings WHERE '20160725' BETWEEN Arrival_Date AND departure_Date

My query returns returns a count of 45 adults on the 25/07/2016 where as the example you setout returns 37 adults for the same day.

Any ideas why this could be?

Thanks again,

ScottPletcher · November 3, 2016, 5:03pm

No, I'm not sure what's up.

My query returns

What "query"?

This query:
SELECT quote_no, Arrival_Date, departure_Date, no_adults, no_children FROM wce_bookings WHERE '20160725' BETWEEN Arrival_Date AND departure_Date
COMPUTE SUM(no_adults) /not allowed on newer versions of SQL Server/

does show 37 adults.

mshsilver · November 3, 2016, 6:16pm

Hi, thanks for the reply.

This query is what you provided based on my sample data. If you look at the 25/7/2016 (GMT) there are 44 adults returned.


WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0) AS date,
    SUM(No_Adults) AS Adult,
    DATENAME(YEAR, wb.Arrival_date) AS Year
FROM wce_bookings wb
INNER JOIN cteTally10K t ON 
    t.number BETWEEN 0 AND DATEDIFF(DAY, wb.Arrival_Date, wb.Departure_date)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0), DATENAME(YEAR, wb.Arrival_date)
ORDER BY date

Now if you run these queries against the same sample data for the same date 25/07/2016 the result is 37 adults. There should be the same number of adults in both queries. I just can't see why there is a difference. Thanks for looking.

SELECT quote_no, Arrival_Date, departure_Date, no_adults, no_children FROM wce_bookings WHERE Arrival_Date <= '20160725' and departure_Date >= '20160725'
SELECT sum(No_Adults) FROM wce_bookings WHERE Arrival_Date <= '20160725' and departure_Date >= '20160725'

Here is the sample data:

USE [testdb]
GO
/****** Object:  Table [dbo].[wce_bookings]    Script Date: 10/24/2016 16:06:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wce_bookings](
	[UniqueID] [varchar](16) NOT NULL,
	[Quote_No] [int] NOT NULL,
	[No_Adults] [int] NULL,
	[No_Children] [int] NULL,
	[Arrival_Date] [datetime] NULL,
	[Departure_Date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
print 'Processed 100 total records'
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf37ko27gjf7tea', 552, 6, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A63E00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf37no3cwqghnlj', 593, 6, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf381928uub4jnk', 543, 8, 0, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf38d23sd8cab1f', 554, 6, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf38p34ef278n67', 604, 4, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A62D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf39h93s6fccgl5', 565, 4, 1, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf39um296tf0to1', 549, 2, 1, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3dgf2tffb3u9d', 545, 4, 2, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3efq3016jlyd8', 596, 4, 0, CAST(0x0000A6260107AC00 AS DateTime), CAST(0x0000A62A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3fx939mli1jf6', 566, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3g593oszj2tuk', 564, 2, 1, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3gwq30tu9oen6', 597, 2, 0, CAST(0x0000A6260107AC00 AS DateTime), CAST(0x0000A62D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3jpa3f4gcgqr9', 568, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3kdb319ngeu67', 570, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3kq334k452cq7', 557, 2, 2, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3lal2kvs7hukf', 546, 6, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mdn2i8z2szhd', 550, 2, 2, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63E00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mfj3m7s4bwnk', 588, 7, 0, CAST(0x0000A61E0107AC00 AS DateTime), CAST(0x0000A62500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3mpo2r6e1rx15', 553, 2, 2, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3p5i3sl6i0iyl', 579, 3, 0, CAST(0x0000A61C0107AC00 AS DateTime), CAST(0x0000A62000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3q783bo2gg8ie', 561, 4, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3qg83aq3ecg6d', 562, 2, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3saz324hhih34', 602, 8, 0, CAST(0x0000A6280107AC00 AS DateTime), CAST(0x0000A62C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3u2b3tzm4lfi4', 569, 2, 2, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3vaa334njwpa4', 567, 6, 1, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3xi43yx3lyoe9', 560, 2, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3xin3icte341c', 591, 8, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y143xgjj9qie', 558, 3, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y243l6jdi2s3', 559, 2, 2, CAST(0x0000A6160107AC00 AS DateTime), CAST(0x0000A61D00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3y7o2tppjax8f', 551, 3, 2, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63700A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3yv23olkdtyqf', 555, 3, 0, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'apf3z2v3197c6hy6', 601, 6, 0, CAST(0x0000A6270107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3gvg3g2dddgy4', 810, 0, 0, CAST(0x0000A61100F73140 AS DateTime), CAST(0x0000A61F00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3lk13py6fec6h', 805, 0, 0, CAST(0x0000A65300F73140 AS DateTime), CAST(0x0000A661009450C0 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3nja3rls8ge9f', 807, 0, 0, CAST(0x0000A62900F73140 AS DateTime), CAST(0x0000A63900735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3phf3tzdd8ol6', 809, 0, 0, CAST(0x0000A60300F73140 AS DateTime), CAST(0x0000A60D00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3q2931vm62rx5', 806, 0, 0, CAST(0x0000A62200F73140 AS DateTime), CAST(0x0000A62D0107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'asf3rrb3qlvj8lt6', 808, 0, 0, CAST(0x0000A62A00F73140 AS DateTime), CAST(0x0000A636010FE960 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf351p3w74j1job', 959, 4, 0, CAST(0x0000A6290107AC00 AS DateTime), CAST(0x0000A63000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3cq62ok98scif', 956, 3, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3izc2h1aiydth', 957, 2, 1, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3uu52bfxazlzi', 955, 2, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3wrx10kok4sre', 952, 2, 2, CAST(0x0000A6400107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bxf3x4435paild58', 958, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dsf3bqi3sfng1jx3', 816, 6, 0, CAST(0x0000A6220107AC00 AS DateTime), CAST(0x0000A62900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dxf31hs2jzac6tch', 962, 2, 2, CAST(0x0000A6400107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'dxf378m20cnpav7h', 961, 2, 2, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'e0g3bg23c2k10gvu', 1057, 3, 0, CAST(0x0000A64E0107AC00 AS DateTime), CAST(0x0000A64F00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'e0g3mhs2vp0lkwcj', 1056, 2, 0, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'epf31r73f7v92osd', 612, 2, 0, CAST(0x0000A5F90107AC00 AS DateTime), CAST(0x0000A5FB00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'esf3sgo2s1h2x23a', 819, 2, 1, CAST(0x0000A6390107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf31hi3qqu2fnbj', 824, 2, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf321i3ak78we6b', 823, 1, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf34lj3xejb53pc', 826, 2, 0, CAST(0x0000A63F0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf36pg2tx1l37mj', 821, 2, 3, CAST(0x0000A6300107AC00 AS DateTime), CAST(0x0000A63700A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf3cl92arnklpfi', 820, 4, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'euf3ybj3vbsjrael', 825, 2, 0, CAST(0x0000A63D0107AC00 AS DateTime), CAST(0x0000A64000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'f0g3y4033atkk3vg', 1058, 3, 0, CAST(0x0000A6480107AC00 AS DateTime), CAST(0x0000A64B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf34e028ohdql4i', 300, 6, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf3f7d20dqigdn5', 302, 4, 0, CAST(0x0000A5FB0107AC00 AS DateTime), CAST(0x0000A60200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fmf3w562ow1jjlvg', 301, 0, 0, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A6530107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf34cy1ng0axk7j', 617, 8, 0, CAST(0x0000A62F0107AC00 AS DateTime), CAST(0x0000A63300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf385s1rs31i9gi', 614, 4, 0, CAST(0x0000A62D0107AC00 AS DateTime), CAST(0x0000A63100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf3i5u1q3min7o4', 615, 4, 0, CAST(0x0000A62D0107AC00 AS DateTime), CAST(0x0000A63100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'fpf3krx1jzx2z5a1', 616, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf30ph3ahag6fcb', 841, 2, 2, CAST(0x0000A61400A4CB80 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf30xd2lwti9jie', 831, 0, 0, CAST(0x0000A63D00F73140 AS DateTime), CAST(0x0000A64300735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf37ag2dgbij1d7', 833, 2, 2, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3nhj340y8p411', 842, 2, 3, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3sdl2s9pg58ie', 835, 0, 0, CAST(0x0000A61100F73140 AS DateTime), CAST(0x0000A61F00735B40 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3wxj39wgibjkb', 843, 2, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'guf3y8n2lb61o56i', 838, 6, 0, CAST(0x0000A5FD0107AC00 AS DateTime), CAST(0x0000A60100A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g31k82jv8eymc8', 1064, 0, 0, CAST(0x0000A6540107AC00 AS DateTime), CAST(0x0000A65800A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g3kvt20k6zjsb2', 1070, 2, 0, CAST(0x0000A64C0107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h0g3opn203plhaq9', 1069, 2, 0, CAST(0x0000A6530107AC00 AS DateTime), CAST(0x0000A66000A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h3g3z0h3r3oc2h14', 1142, 1, 0, CAST(0x0000A6360107AC00 AS DateTime), CAST(0x0000A67300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hpf38pm2yw2fnx8h', 622, 2, 0, CAST(0x0000A6370107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf38pl2eqaihibl', 845, 4, 3, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf3pd33cla2lt27', 847, 1, 0, CAST(0x0000A63C0107AC00 AS DateTime), CAST(0x0000A64300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'huf3wdg2m01d3lp3', 844, 4, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A65300A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3bsn23l7ehhqa', 970, 3, 2, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3ogm2zyzhyl8j', 969, 6, 1, CAST(0x0000A6450107AC00 AS DateTime), CAST(0x0000A64C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hxf3qi9201t1lw13', 968, 2, 0, CAST(0x0000A6470107AC00 AS DateTime), CAST(0x0000A65500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3r1g204a9aoo8', 1075, 4, 0, CAST(0x0000A62E0107AC00 AS DateTime), CAST(0x0000A63500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ipf3x8t2qwo52f9l', 628, 4, 0, CAST(0x0000A64A0107AC00 AS DateTime), CAST(0x0000A65400A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'iuf3b4j3yu3esbvg', 851, 2, 0, CAST(0x0000A60E0107AC00 AS DateTime), CAST(0x0000A61200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ixf3bum2jc4huou4', 973, 4, 0, CAST(0x0000A64E0107AC00 AS DateTime), CAST(0x0000A65900A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ixf3lc238ibc3pfc', 975, 4, 1, CAST(0x0000A63E0107AC00 AS DateTime), CAST(0x0000A64500A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'jxf3zck4ie5ina1f', 977, 2, 1, CAST(0x0000A6530107AC00 AS DateTime), CAST(0x0000A65A00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kjf39r33ru24mozi', 207, 2, 0, CAST(0x0000A60B0107AC00 AS DateTime), CAST(0x0000A61200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kpf32ts2jfzdy1bb', 630, 0, 0, CAST(0x0000A6130107AC00 AS DateTime), CAST(0x0000A61A0107AC00 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf342d4wwg6nz3j', 856, 4, 2, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf39fd4wl4drkle', 857, 7, 1, CAST(0x0000A61B0107AC00 AS DateTime), CAST(0x0000A62200A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf39vc43zed5f56', 855, 4, 2, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3cvb4rqt7hjxc', 853, 2, 1, CAST(0x0000A60D0107AC00 AS DateTime), CAST(0x0000A61400A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3lqd4472e1gtk', 858, 3, 0, CAST(0x0000A6040107AC00 AS DateTime), CAST(0x0000A60B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3n7i4eb5dum1i', 861, 4, 0, CAST(0x0000A6150107AC00 AS DateTime), CAST(0x0000A61C00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3v4b4eoheqat8', 852, 2, 0, CAST(0x0000A5FF0107AC00 AS DateTime), CAST(0x0000A60600A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3xte4dib39xk6', 860, 4, 0, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kuf3zic4kt157pma', 854, 4, 4, CAST(0x0000A6140107AC00 AS DateTime), CAST(0x0000A61B00A4CB80 AS DateTime))
INSERT [dbo].[wce_bookings] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'kxf3lyf3wtx383pk', 979, 1, 0, CAST(0x0000A6000107AC00 AS DateTime), CAST(0x0000A63600A4CB80 AS DateTime))
GO

ScottPletcher · November 3, 2016, 6:57pm

OK; it comes down to these two rows, which the original query had but yours didn't:

e0g3bg23c2k10gvu 1057 3 0 2016-07-25 16:00:00.000 2016-07-26 10:00:00.000
ixf3bum2jc4huou4 973 4 0 2016-07-25 16:00:00.000 2016-08-05 10:00:00.000

That's because this query's conditions:
SELECT quote_no, Arrival_Date, departure_Date, no_adults, no_children FROM wce_bookings
WHERE Arrival_Date <= '20160725' and departure_Date >= '20160725'

Would leave out any row after midnight on 25 Jul 2016, since the default time is '00:00:00'. That is, that condition is the same as:
WHERE Arrival_Date <= '20160725 00:00:00.000'
The actual Arrival_Date in the rows are '20160725 16:00', which is not <= that value, so the rows are not included.

My code assumed that you wanted the row counted if they arrived at any time during that day.

mshsilver · November 4, 2016, 8:34am

Ahh the times! Thanks for the help with that. You live and learn

mshsilver · December 7, 2016, 5:53pm

Hi Scott,

You kindly helped me with this query and I have hit another little issue that I can't get my head round.

To recap, I am trying to work out on any specific day how many adults and children are booked on a holiday. This has been working a treat until I hit some dates that are duplicated in the query.

Here is the sample data.


USE [TESTDB]
GO
/****** Object:  Table [dbo].[booking_test]    Script Date: 12/07/2016 17:51:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[booking_test](
	[UniqueID] [varchar](16) NOT NULL,
	[Quote_No] [int] NOT NULL,
	[No_Adults] [int] NULL,
	[No_Children] [int] NULL,
	[Arrival_Date] [datetime2](3) NULL,
	[Departure_Date] [datetime2](3) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'06g3d6a30y6b91m2', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'06g3fx930hy4wdrh', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'06g3jaa379qeg6bj', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'06g3n3a3vv54862j', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg35912mu96z8cl', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3jh02948k4pue', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3kh02dfpbmtac', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3kh02h24ls51l', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3lh02eluilt23', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3lh02qd59lg8d', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'3bg3z612y1sh0os3', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g35w334vi4tk5l', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g36243lqgkxu37', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g36j83am6lng2a', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3ie8374d7o3kk', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3jl433mx65hzb', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3lf43njefykp5', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3o883pggaxgwi', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3vf33m1jbzjxh', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'46g3x7330x2j3vp6', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg31cm2me3bxatb', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg35xm2u8tj066w', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3a7w26eqkeqa2', 1210, 2, 0, CAST(0x0300E86E03463C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3dgm2m2hgy4p1', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3ghm2pul1t90a', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3l1w254e8as76', 1210, 2, 0, CAST(0x0300E86E03463C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3mw134zd854xf', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3tjm2wmv1qh66', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'8bg3trm2oujhjuvd', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'9bg3c8g30p1esvxf', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'9bg3fcg30gbzhfzi', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'9bg3yeg37v1ac705', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'bbg3sll2hal4ix28', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3jbh2cby91x63', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3nah25b6lci6c', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3w1h2qirjhg16', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3wmk24g9aoyq7', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3zgh2387alms2', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ebg3zrk2qolh22c8', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg30gp32n61iny9', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg32nn3ysr63l5i', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3bqn39g3ewbn1', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3d7p30g8dekr6', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3hin3xj6enuo9', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3ofj335qdnod7', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3owo3pda39137', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3p4p36jf2z5rc', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3s0o3glmebo6l', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'gbg3weq3e3dh7ge8', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'h5g3msn2urwgq4dc', 1154, 0, 0, CAST(0x0300E86E033D3C0B00 AS DateTime2), CAST(0x03005125024D3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'hbg3h652m8qf37f5', 1396, 2, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g39ud2jyod136i', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3nld2zao53566', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3pqd20xwc4c9f', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3rog2c7y1avi8', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3scd2nxsb6o39', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3sfd2lz180dzo', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3vkd2ytca2yh7', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'i0g3whd2umt96k9j', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ibg3xs72l31dmz27', 1432, 3, 0, CAST(0x0300E86E03493C0B00 AS DateTime2), CAST(0x03005125024E3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'l5g3ybl21y548r0k', 1074, 2, 0, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'o8g32782vzl65fb9', 1334, 4, 0, CAST(0x0300E86E034D3C0B00 AS DateTime2), CAST(0x0300512502623C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'o8g37v72n5vjee9h', 1334, 4, 0, CAST(0x0300E86E034D3C0B00 AS DateTime2), CAST(0x0300512502623C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'o8g3r082madgn2l3', 1334, 4, 0, CAST(0x0300E86E034D3C0B00 AS DateTime2), CAST(0x0300512502623C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3cce2cp0ag6wf', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3cce2jpm1drf4', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3cce2nsfio5pi', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3dce2log5u29l', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3dce2yo3ea986', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p2g3v1g2rbdgjhp8', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p5g30ht23swbhlr2', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p5g33tt2fdke063l', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p5g3h3u2a01clrbj', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'p5g3xvt2mi4fnk45', 1187, 2, 0, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x0300512502603C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'pbg3qrz17f9glp5g', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'sbg3kb12mb7b0qu1', 1440, 3, 7, CAST(0x0300E86E034B3C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'ubg39w33k3ykoku9', 1246, 2, 1, CAST(0x0300E86E03413C0B00 AS DateTime2), CAST(0x03005125024F3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'x5g3gsw20ua2w1ub', 1210, 2, 0, CAST(0x0300E86E03463C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'x5g3xpw29v6aw3ve', 1210, 2, 0, CAST(0x0300E86E03463C0B00 AS DateTime2), CAST(0x03005125024A3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'y2g3l3h3wen59s9e', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))
INSERT [dbo].[booking_test] ([UniqueID], [Quote_No], [No_Adults], [No_Children], [Arrival_Date], [Departure_Date]) VALUES (N'y2g3r9h3pz1215q2', 1095, 4, 0, CAST(0x0300E86E03443C0B00 AS DateTime2), CAST(0x03005125024B3C0B00 AS DateTime2))

And here is the Query that retrieves the dates and counts. If you look at 2nd of January 2017 and onwards to the 8th you you will see duplcaition for these days and i can't see why.


WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0) AS date,
    SUM(No_Adults) AS Adult,
    DATENAME(YEAR, wb.Arrival_date) AS Year
FROM booking_test wb
INNER JOIN cteTally10K t ON 
    t.number BETWEEN 0 AND DATEDIFF(DAY, wb.Arrival_Date, wb.Departure_date)
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, wb.Arrival_Date) + t.number, 0), DATENAME(YEAR, wb.Arrival_date)
ORDER BY date

Any advice would be greatly appreciated.

Many thanks.