December 8, 2016 at 3:35 am
Hi All,
I am trying to work out on any specific day how many adults and children are booked on a holiday. This code below 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 between 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.
December 8, 2016 at 3:56 am
It's because for those particular values of date, you have two separate values of Year. Presumably this is caused by arrival date and departure date being in different years.
John
December 8, 2016 at 4:01 am
Thanks John,
That makes sense! Do you see anyway around this with that code? This stuff is all new to me so i'm not an expert.
Many thanks.
December 8, 2016 at 4:27 am
Can you not just dispense with the Year column? What do you need it to represent?
John
December 8, 2016 at 5:25 am
Yea the year column isn't needed there. I have removed it and I still get duplication on the days in January.
I just need a true reflection (count) of those adults and children on site between the arrival and departure dates of the bookings.
I must be missing something else. If you spot it please let me know.
December 8, 2016 at 5:29 am
Did you remove it from the SELECT list and the GROUP BY clause?
John
Edit - corrected italics tags
December 8, 2016 at 5:32 am
Sorry, correction! I had left a reference to the year in the query. I think this is now resolved! Many thanks. I will come back if I hit another issue.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply