October 24, 2016 at 9:30 am
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
October 24, 2016 at 9:47 am
Do you need to include all dates? Or just the dates with bookings? The solution could be slightly different.
October 24, 2016 at 9:53 am
If you're counting values per day, you're missing the Calendar table. If you left join the Calendar table to the table of scheduled events, then it's stupid easy. You need all the days between these two dates:
SELECT MIN(Arrival_Date) AS FirstDate
,MAX(Arrival_Date) AS LastDate
FROM [wce_bookings];
Then you join your Calendar table to this.
SELECT c.Date, b.BookingID
FROM Calendar c LEFT JOIN Booking b ON c.CalendarDate BETWEEN b.StartDate and b.EndDate
then you can just count them
October 24, 2016 at 10:07 am
Hi Thanks for your reply. All those dates are bookings. So each row equates to a booking with an arrival and departure date. Is that what you meant?
Thanks again.
October 24, 2016 at 10:10 am
Thanks pietlinden for your reply too. I don't have a calendar table. Is this something that is a standard SQL table structure I copy then use with your query? Thanks for your help.
October 24, 2016 at 10:13 am
You need a calendar table, though, so you can force the existence of days where there are no bookings. You do that like this:
SELECT ...
FROM Calendar c LEFT JOIN Booking b ON c.CalendarDate BETWEEN b.StartDate and b.EndDate
then you can wrap that in another query and count for each day...
SELECT CalendarDate, COUNT(*) As DailyBookings
FROM ( ... inner query ...)
GROUP BY CalendarDate
ORDER BY CalendarDate;
October 24, 2016 at 10:18 am
Here are 2 options, depending on what you want. It's simulating a calendar table, but you might want to consider a full calendar table in your system to make some of your queries easier.
--Option 1: Generate dates and assign bookings to them.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
),
cteCalendar(CalDate) AS(
SELECT DATEADD( dd, n, '2013/01/01') AS CalDate
FROM cteTally
)
SELECT c.CalDate,
SUM( b.No_Adults) AS No_Adults,
SUM( b.No_Children) AS No_Children
FROM [wce_bookings] b
RIGHT
JOIN cteCalendar c ON CAST( b.Arrival_Date AS date) <= c.CalDate
AND b.Departure_Date > c.CalDate
GROUP BY CalDate
ORDER BY CalDate;
;
GO
--Option 2: Assign dates to bookings. This will omit dates without bookings
CREATE FUNCTION Generate_Dates(
@StartDate date,
@EndDate date
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 UNION ALL
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT DATEADD( dd, n, @StartDate) AS CalDate
FROM cteTally;
GO
SELECT D.CalDate,
SUM( b.No_Adults) AS No_Adults,
SUM( b.No_Children) AS No_Children
FROM [wce_bookings] b
CROSS APPLY dbo.Generate_Dates( b.Arrival_Date, b.Departure_Date) D
GROUP BY CalDate
ORDER BY CalDate;
October 24, 2016 at 11:10 am
Firstly, thank you both for all that input. Lots to think about and play with.
Luis Cazares - those two options are great, I think i can use those to achieve what i need and will have a play. Thank you.
pietlinden - those links are great, I can see how they too are going to help me achieve the end results, they go into great detail. Thank you for those.
I might well come back with more questions 🙂
October 24, 2016 at 5:08 pm
Luis' answer is super cool...
The Calendar table has some advantages... you can filter out any pattern you want (certain days of the week, days of year, etc) and its super easy. Really depends on what your business requirements are, though. If you can share those, you'll get a better answer.
October 25, 2016 at 1:24 am
Hi Luis Cazares,
Could I just pick your brains please. Option 2 seems to be working great but I would like to change the table reference form wce_bookings to wce_quotes. I have tried changing the table name but then I get an error. 'Msg 1014, Level 15, State 1, Line 3 TOP clause contains an invalid value.'
The SQL is a little more complex than I am use to so I am guessing that this is also controlled in the function but I can't see how. Could you explain how to alter this please so I can better understand how this is referenced.
Many thanks.
October 25, 2016 at 1:25 am
Hi Luis Cazares,
Could I just pick your brains please. Option 2 seems to be working great but I would like to change the table reference form wce_bookings to wce_quotes. I have tried changing the table name but then I get an error. 'Msg 1014, Level 15, State 1, Line 3 TOP clause contains an invalid value.'
The SQL is a little more complex than I am use to so I am guessing that this is also controlled in the function but I can't see how. Could you explain how to alter this please so I can better understand how this is referenced.
Many thanks.
October 25, 2016 at 1:26 am
Hi Luis Cazares,
Could I just pick your brains please. Option 2 seems to be working great but I would like to change the table reference form wce_bookings to wce_quotes. I have tried changing the table name but then I get an error. 'Msg 1014, Level 15, State 1, Line 3 TOP clause contains an invalid value.'
The SQL is a little more complex than I am use to so I am guessing that this is also controlled in the function but I can't see how. Could you explain how to alter this please so I can better understand how this is referenced.
Many thanks.
October 25, 2016 at 7:12 am
I'm posting the function again with some comments. Hopefully those comments will help you understand how the query is working. I should strongly recommend that you read the following article on Tally Tables which is the base for this code: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
CREATE FUNCTION Generate_Dates(
@StartDate date,
@EndDate date
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
--Create 10 rows
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
--Create 10*10 = 100 rows
SELECT a.n FROM E a, E b
),
E4(n) AS(
--Create 100*100 = 10,000 rows
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 --Add row for initial value (start date without change)
UNION ALL
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate)) --Limit the rows to the number of days in the range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n --Assign consecutive numbers to the rows from 1 to N
FROM E4
)
SELECT DATEADD( dd, n, @StartDate) AS CalDate --Add days to the start date
FROM cteTally;
Now, the problem that you're having, is because the DATEDIFF is returning an invalid value to the TOP clause. This could be cause by a NULL value which you would have to validate to prevent errors. Be aware that an incorrect range where the start date is greater than the end date would also cause errors.
Play with the function and understand how does it works. Run the CTE one step at a time to see how the result is being created. Here's the function in the form of an independent query.
DECLARE
@StartDate date,
@EndDate date
WITH
E(n) AS(
--Create 10 rows
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
--Create 10*10 = 100 rows
SELECT a.n FROM E a, E b
),
E4(n) AS(
--Create 100*100 = 10,000 rows
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 --Add row for initial value (start date without change)
UNION ALL
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate)) --Limit the rows to the number of days in the range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n --Assign consecutive numbers to the rows from 1 to N
FROM E4
)
SELECT DATEADD( dd, n, @StartDate) AS CalDate --Add days to the start date
FROM cteTally;
October 25, 2016 at 11:43 am
Just one item of consideration that I wanted to throw out there. The bookings can change from day to day, since from a business perspective I imagine each booking can be canceled. It is not a bad idea to take daily snapshots as not only can you gather average bookings looking ahead, but average number of cancels as well.
Are you reporting on this data directly into a report? This is a candidate for a fact table for a data cube BTW.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply