February 29, 2012 at 7:46 am
Hi
I wanted to write a query to search a hotel between two dates
These are the following tables
1.Hotel
2.Room
3.Allocation
4.Rate
Following is the script with data
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Room](
[RoomID] [int] IDENTITY(1,1) NOT NULL,
[RoomName] [varchar](100) NOT NULL,
[HotelID] [int] NOT NULL,
[RoomTypeID] [tinyint] NOT NULL,
[IncludedAdult] [tinyint] NOT NULL,
[IncludedChild] [tinyint] NOT NULL,
[MaxAdult] [tinyint] NOT NULL,
[MaxChild] [tinyint] NOT NULL,
[MaxOccupancy] [tinyint] NOT NULL,
[Description] [varchar](500) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_RoomID] PRIMARY KEY CLUSTERED
(
[RoomID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Inventory]
) ON [Inventory]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Room] ON
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (2, N'SingleLarge', 1, 1, 2, 2, 5, 5, 10, N'good', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (4, N'DoubleBig', 1, 2, 1, 1, 2, 2, 4, N'good', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (5, N'single exclusive', 2, 1, 1, 1, 1, 1, 2, N'ccc', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (6, N'double small', 3, 2, 1, 1, 4, 4, 8, N'ggg', 1)
SET IDENTITY_INSERT [dbo].[Room] OFF
/****** Object: Table [dbo].[Rate] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rate](
[RateID] [bigint] NOT NULL,
[RoomID] [int] NOT NULL,
[AgencyID] [int] NULL,
[Date] [date] NOT NULL,
[Rate] [numeric](19, 5) NOT NULL,
[IsActive] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Inventory]
) ON [Inventory]
GO
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (1, 2, 1, CAST(0x26350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (2, 2, 1, CAST(0x27350B00 AS Date), CAST(50.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (3, 2, 1, CAST(0x28350B00 AS Date), CAST(60.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (4, 2, 1, CAST(0x29350B00 AS Date), CAST(26.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (5, 2, 2, CAST(0x26350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (6, 2, 2, CAST(0x27350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (7, 4, 1, CAST(0x26350B00 AS Date), CAST(34.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (8, 4, 1, CAST(0x27350B00 AS Date), CAST(56.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (9, 4, 1, CAST(0x2A350B00 AS Date), CAST(556.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (10, 4, 1, CAST(0x2B350B00 AS Date), CAST(556.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (11, 4, 1, CAST(0x2C350B00 AS Date), CAST(536.00000 AS Numeric(19, 5)), 1)
/****** Object: Table [dbo].[Hotel] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hotel](
[HotelID] [int] IDENTITY(1,1) NOT NULL,
[HotelName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Hotel] PRIMARY KEY CLUSTERED
(
[HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Inventory]
) ON [Inventory]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Hotel] ON
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (1, N'Taj hotel')
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (2, N'Ashoka hotel')
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (3, N'Trident')
SET IDENTITY_INSERT [dbo].[Hotel] OFF
/****** Object: Table [dbo].[Allocation] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Allocation](
[AllocationID] [bigint] NOT NULL,
[RoomID] [int] NOT NULL,
[AgencyID] [int] NULL,
[Date] [date] NOT NULL,
[TotalAllotment] [smallint] NOT NULL,
[Used] [smallint] NOT NULL,
[Available] [smallint] NOT NULL,
[IsActive] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[AllocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Inventory]
) ON [Inventory]
GO
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (1, 2, 1, CAST(0x26350B00 AS Date), 10, 5, 5, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (2, 2, 1, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (3, 2, 1, CAST(0x28350B00 AS Date), 10, 7, 3, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (4, 2, 1, CAST(0x29350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (5, 4, 1, CAST(0x26350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (6, 4, 1, CAST(0x27350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (7, 5, 1, CAST(0x26350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (8, 5, 1, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (9, 5, 1, CAST(0x28350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (10, 2, 2, CAST(0x26350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (11, 2, 2, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (12, 2, 2, CAST(0x28350B00 AS Date), 10, 7, 3, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (13, 2, 2, CAST(0x29350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (14, 6, 1, CAST(0x26350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (15, 6, 1, CAST(0x27350B00 AS Date), 10, 5, 5, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (16, 6, 1, CAST(0x2B350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (17, 6, 1, CAST(0x2C350B00 AS Date), 10, 1, 9, 1)
If checkin date = 2012-01-01 AND Checkoutdate = 12-01-04
Then From allocation table it has to get Room on this date only(1,2,3,4) only these four specific dates no other dates in this range means
It should ignore other dates like 1,2 or 1,3 or 2,3 or 1,4 or 2,4 or 3,4
Same logic has to apply on Rate table also
In allocation And rate table the entry is done on aspecific date no date range
I have able to get the Hotel availability based on the allocation table but not able to get the desired result including rate also
here is the coding what i had done
DECLARE @Checkin date = '2012-01-01'
DECLARE @Checkout date = '2012-01-04'
DECLARE @Checkoutin date
SET @Checkoutin = DATEADD(DAY,1,@Checkout)
--SELECT @Checkoutin
--SELECT @Checkout
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkoutin)
--SELECT @diff
SELECT * FROM(
SELECT SUM(t.Counts)OVER(PARTITION BY HotelID,RoomID,AgencyID)as SUM1,
HotelID,
HotelName,
RoomID,
RoomName,
AgencyID,
[Date],
TotalAllotment,
Used,
Available,
IsActive
FROM(
SELECT
ROW_NUMBER()OVER(PARTITION BY H.HotelID,R.RoomID,A.AgencyID ORDER BY H.HotelID,R.RoomID) as Rownum,
1 AS Counts,
H.HotelID,
H.HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.[Date],
A.TotalAllotment,
A.Used,
A.Available,
A.IsActive
--v.number
FROM dbo.Hotel AS H
JOIN dbo.Room AS R
ON H.HotelID = R.HotelID
Join dbo.Allocation AS A
ON R.RoomID = A.RoomID AND A.IsActive = 1 AND A.[Date] between @Checkin AND @Checkout ) AS t ) AS B WHERE B.SUM1 = @diff--AS t where t.Rownum between 1 AND @diff
--WHERE (A.[Date] between @Checkin AND @Checkout ) AS t where t.Rownum between 1 AND @diff
--) AS t WHERE SUM1 = @diff
--INNER JOIN master..spt_values v ON v.type = 'P' AND v.number = DATEDIFF(DAY,@Checkin,@Checkout)
--join dbo.Calendar AS C
--ON A.[Date] = C.CalndarDate
--WHERE (A.[Date] between @Checkin AND @Checkout )
I want the hotels which are available on allocation table and rate table between two dates..it should display dates 1,2,3,4(only this combination) with hotelname and allocation and rates
Any help appreciated
thank you
February 29, 2012 at 8:23 am
First of all, I had to correct your DDL. The "ON [Inventory]" stuff was causing it to fail. This version ran for me.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Room](
[RoomID] [int] IDENTITY(1,1) NOT NULL,
[RoomName] [varchar](100) NOT NULL,
[HotelID] [int] NOT NULL,
[RoomTypeID] [tinyint] NOT NULL,
[IncludedAdult] [tinyint] NOT NULL,
[IncludedChild] [tinyint] NOT NULL,
[MaxAdult] [tinyint] NOT NULL,
[MaxChild] [tinyint] NOT NULL,
[MaxOccupancy] [tinyint] NOT NULL,
[Description] [varchar](500) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_RoomID] PRIMARY KEY CLUSTERED
(
[RoomID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Room] ON
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (2, N'SingleLarge', 1, 1, 2, 2, 5, 5, 10, N'good', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (4, N'DoubleBig', 1, 2, 1, 1, 2, 2, 4, N'good', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (5, N'single exclusive', 2, 1, 1, 1, 1, 1, 2, N'ccc', 1)
INSERT [dbo].[Room] ([RoomID], [RoomName], [HotelID], [RoomTypeID], [IncludedAdult], [IncludedChild], [MaxAdult], [MaxChild], [MaxOccupancy], [Description], [IsActive]) VALUES (6, N'double small', 3, 2, 1, 1, 4, 4, 8, N'ggg', 1)
SET IDENTITY_INSERT [dbo].[Room] OFF
/****** Object: Table [dbo].[Rate] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Rate](
[RateID] [bigint] NOT NULL,
[RoomID] [int] NOT NULL,
[AgencyID] [int] NULL,
[Date] [date] NOT NULL,
[Rate] [numeric](19, 5) NOT NULL,
[IsActive] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]
GO
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (1, 2, 1, CAST(0x26350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (2, 2, 1, CAST(0x27350B00 AS Date), CAST(50.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (3, 2, 1, CAST(0x28350B00 AS Date), CAST(60.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (4, 2, 1, CAST(0x29350B00 AS Date), CAST(26.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (5, 2, 2, CAST(0x26350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (6, 2, 2, CAST(0x27350B00 AS Date), CAST(20.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (7, 4, 1, CAST(0x26350B00 AS Date), CAST(34.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (8, 4, 1, CAST(0x27350B00 AS Date), CAST(56.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (9, 4, 1, CAST(0x2A350B00 AS Date), CAST(556.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (10, 4, 1, CAST(0x2B350B00 AS Date), CAST(556.00000 AS Numeric(19, 5)), 1)
INSERT [dbo].[Rate] ([RateID], [RoomID], [AgencyID], [Date], [Rate], [IsActive]) VALUES (11, 4, 1, CAST(0x2C350B00 AS Date), CAST(536.00000 AS Numeric(19, 5)), 1)
/****** Object: Table [dbo].[Hotel] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hotel](
[HotelID] [int] IDENTITY(1,1) NOT NULL,
[HotelName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Hotel] PRIMARY KEY CLUSTERED
(
[HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Hotel] ON
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (1, N'Taj hotel')
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (2, N'Ashoka hotel')
INSERT [dbo].[Hotel] ([HotelID], [HotelName]) VALUES (3, N'Trident')
SET IDENTITY_INSERT [dbo].[Hotel] OFF
/****** Object: Table [dbo].[Allocation] Script Date: 02/29/2012 19:56:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Allocation](
[AllocationID] [bigint] NOT NULL,
[RoomID] [int] NOT NULL,
[AgencyID] [int] NULL,
[Date] [date] NOT NULL,
[TotalAllotment] [smallint] NOT NULL,
[Used] [smallint] NOT NULL,
[Available] [smallint] NOT NULL,
[IsActive] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[AllocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY]
GO
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (1, 2, 1, CAST(0x26350B00 AS Date), 10, 5, 5, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (2, 2, 1, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (3, 2, 1, CAST(0x28350B00 AS Date), 10, 7, 3, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (4, 2, 1, CAST(0x29350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (5, 4, 1, CAST(0x26350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (6, 4, 1, CAST(0x27350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (7, 5, 1, CAST(0x26350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (8, 5, 1, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (9, 5, 1, CAST(0x28350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (10, 2, 2, CAST(0x26350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (11, 2, 2, CAST(0x27350B00 AS Date), 10, 4, 6, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (12, 2, 2, CAST(0x28350B00 AS Date), 10, 7, 3, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (13, 2, 2, CAST(0x29350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (14, 6, 1, CAST(0x26350B00 AS Date), 10, 3, 7, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (15, 6, 1, CAST(0x27350B00 AS Date), 10, 5, 5, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (16, 6, 1, CAST(0x2B350B00 AS Date), 10, 2, 8, 1)
INSERT [dbo].[Allocation] ([AllocationID], [RoomID], [AgencyID], [Date], [TotalAllotment], [Used], [Available], [IsActive]) VALUES (17, 6, 1, CAST(0x2C350B00 AS Date), 10, 1, 9, 1)
--If checkin date = 2012-01-01 AND Checkoutdate = 12-01-04
SELECT * FROM Room
SELECT * FROM Rate
SELECT * FROM Hotel
SELECT * FROM Allocation
I assume you only want the rate by AgencyID. If that is the case, see if this is close to what you're looking for. Note that AgencyID=2 does not have a rate for RoomID=2 on the 3rd and 4th.
DECLARE @Checkin date = '2012-01-01'
DECLARE @Checkout date = '2012-01-04'
DECLARE @Checkoutin date
SET @Checkoutin = DATEADD(DAY,1,@Checkout)
--SELECT @Checkoutin
--SELECT @Checkout
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkoutin)
--SELECT @diff
SELECT B.*, Rate FROM(
SELECT SUM(t.Counts)OVER(PARTITION BY HotelID,RoomID,AgencyID)as SUM1,
HotelID,
HotelName,
RoomID,
RoomName,
AgencyID,
[Date],
TotalAllotment,
Used,
Available,
IsActive
FROM(
SELECT
ROW_NUMBER()OVER(PARTITION BY H.HotelID,R.RoomID,A.AgencyID ORDER BY H.HotelID,R.RoomID) as Rownum,
1 AS Counts,
H.HotelID,
H.HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.[Date],
A.TotalAllotment,
A.Used,
A.Available,
A.IsActive
--v.number
FROM dbo.Hotel AS H
JOIN dbo.Room AS R
ON H.HotelID = R.HotelID
Join dbo.Allocation AS A
ON R.RoomID = A.RoomID AND A.IsActive = 1 AND A.[Date] between @Checkin AND @Checkout ) AS t ) AS B
LEFT JOIN dbo.Rate ON B.RoomID = Rate.RoomID and B.[Date] = Rate.[Date] and B.AgencyID = Rate.AgencyID
WHERE B.SUM1 = @diff--AS t where t.Rownum between 1 AND @diff
--WHERE (A.[Date] between @Checkin AND @Checkout ) AS t where t.Rownum between 1 AND @diff
--) AS t WHERE SUM1 = @diff
--INNER JOIN master..spt_values v ON v.type = 'P' AND v.number = DATEDIFF(DAY,@Checkin,@Checkout)
--join dbo.Calendar AS C
--ON A.[Date] = C.CalndarDate
--WHERE (A.[Date] between @Checkin AND @Checkout )
I honestly don't understand how you got through the hard stuff but couldn't take this last step (unless I'm missing something, which is probably likely).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 29, 2012 at 10:49 pm
thanks dwain for the help.
Actually i got this result..didn't update the script with rate...got stuck with this query for almost 2 days.
As you see i use calendar table and tally tables(which didn't work)...with the help i got from internet they mention that calendar table solved very complex problem.
For the result i want to display only Results which had allocation and rate available on 1,2,3,4
in this case it display result only for AgencyID = 1 and not from AgencyID =2 as it don't have any value on date 3 and 4, so it should not display even there is value on 1 and 2 date.
Thanks once again..
February 29, 2012 at 10:53 pm
I thought that might be what you wanted, but didn't try to make it happen.
I believe that I have done something like that before. I need to find that code (it is uses a rather odd correlated subquery) so I can advise you how to integrate it into your query.
Truthfully, I didn't understand exactly how it worked when I used it. At the time, I found I could make it work for me though. It would likely be the same case again.
I'll look around and advise again when I have time.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 6:15 am
Figured out another way to do it without using that weird sub-query! Thank god!
DECLARE @Checkin date = '2012-01-01'
DECLARE @Checkout date = '2012-01-04'
DECLARE @Checkoutin date
SET @Checkoutin = DATEADD(DAY,1,@Checkout)
--SELECT @Checkoutin
--SELECT @Checkout
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkoutin)
--SELECT @diff
SELECT B.* FROM(
SELECT SUM(t.Counts)OVER(PARTITION BY HotelID,RoomID,AgencyID)as SUM1,
HotelID,
HotelName,
RoomID,
RoomName,
AgencyID,
[Date],
TotalAllotment,
Used,
Available,
IsActive,
Rate
FROM(
SELECT
ROW_NUMBER()OVER(PARTITION BY H.HotelID,R.RoomID,A.AgencyID ORDER BY H.HotelID,R.RoomID) as Rownum,
1 AS Counts,
H.HotelID,
H.HotelName,
R.RoomID,
R.RoomName,
A.AgencyID,
A.[Date],
A.TotalAllotment,
A.Used,
A.Available,
A.IsActive,
Rate.Rate
--v.number
FROM dbo.Hotel AS H
JOIN dbo.Room AS R
ON H.HotelID = R.HotelID
Join dbo.Allocation AS A
ON R.RoomID = A.RoomID AND A.IsActive = 1 AND A.[Date] between @Checkin AND @Checkout
INNER JOIN dbo.Rate ON R.RoomID = Rate.RoomID and A.[Date] = Rate.[Date] and A.AgencyID = Rate.AgencyID
) AS t ) AS B
WHERE B.SUM1 = @diff--AS t where t.Rownum between 1 AND @diff
--WHERE (A.[Date] between @Checkin AND @Checkout ) AS t where t.Rownum between 1 AND @diff
--) AS t WHERE SUM1 = @diff
--INNER JOIN master..spt_values v ON v.type = 'P' AND v.number = DATEDIFF(DAY,@Checkin,@Checkout)
--join dbo.Calendar AS C
--ON A.[Date] = C.CalndarDate
--WHERE (A.[Date] between @Checkin AND @Checkout )
In the solution, I moved the LEFT JOIN I used into your derived t table as an INNER JOIN. This leveraged your counting process to eliminate those records with a NULL rate. Of course, I had to add Rate to all the result sets and remove the direct reference from the first SELECT.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 7:05 am
I got this finally now i had to do some testing based on different data...
DECLARE @Checkin date = '2012-01-01'
DECLARE @Checkout date = '2012-01-04'
DECLARE @Checkoutin date
SET @Checkoutin = DATEADD(DAY,1,@Checkout)
--SELECT @Checkoutin
--SELECT @Checkout
DECLARE @diff tinyint
SELECT @diff = DATEDIFF(DAY,@Checkin,@Checkoutin)
--SELECT @diff
SELECT * FROM(
SELECT SUM(t.Counts)OVER(PARTITION BY HotelID,RoomID,AgencyID)as SUM1,
HotelID,
HotelName,
RoomID,
RoomName,
AgencyID,
[Date],
--TotalAllotment,
--Used,
Available,
--IsActive
Rate
FROM(
SELECT
ROW_NUMBER()OVER(PARTITION BY H.HotelID,Ra.RoomID,DD.AgencyID ORDER BY H.HotelID,Ra.RoomID) as Rownum,
1 AS Counts,
H.HotelID,
H.HotelName,
Ra.RoomID,
Ra.RoomName,
DD.AgencyID,
DD.[Date],
--A.TotalAllotment,
--A.Used,
DD.Available,
DD.Rate
--A.IsActive
--v.number
FROM dbo.Hotel AS H
JOIN dbo.Room AS Ra
ON H.HotelID = Ra.HotelID
JOIN
(SELECT A.AllocationID,A.RoomID,A.AgencyID, A.[Date],A.TotalAllotment,A.Used,A.Available,A.IsActive,R.Rate
from dbo.Allocation AS A
JOIN dbo.Rate AS R
ON A.RoomID = R.RoomID AND A.AgencyID = R.AgencyID AND A.[Date] = R.[Date]
WHERE A.Date BETWEEN @Checkin AND @Checkout
AND R.Date BETWEEN @Checkin AND @Checkout
) AS DD
ON Ra.RoomID = DD.RoomID) AS t) AS B WHERE B.SUM1 = @diff
Don't know any performance issue is there with this code
dwain post the code u mention, just want to see the code
thanks once again
March 1, 2012 at 7:13 am
Let me ask, did you find some issues in your original counting process?
I was looking at that thinking there might be some cases that could trip it up, but actually I didn't find one (may have been able to look harder).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 1, 2012 at 7:18 am
Didn't seen your post, thanks once again
i will do some testing and let u know
August 26, 2012 at 12:53 pm
I'm a university student and I'm developing a database for a hotel system ,now I'm stack when I try to check rooms that are available for a specific date,room_status and type of a room.I'm using SQL server 2008, I have two table that m checking on which are : dbo.RoomBooking(Booking_ID,Room_Number,Arrival_date,Departure_date) , dbo.Rooms(Room_Number,Room_type,Room_status)
I hv tried this query: create proc GetAvailableRoom
(@RT_Name char(15), @ArriveDate Date, @DepDate Date)
AS SELECT Room_no from Rooms r ,Room_Type rt
WHERE r.Room_Type_Code = rt.Room_Type_Code and
Room_no NOT IN (Select Room_no FROM Room_Bookings
Where Chck_in_Date Between @ArriveDate and @DepDate and
Chck_out_Date between @ArriveDate and @DepDate)
and rt.Room_Type_Name = @RT_Name and Room_Status = 'Avail'
go
create table Rooms
(Room_no char(4) primary key not null,
Room_Status varchar(20) not null,
Room_Type_Code char(4) foreign key references Room_Type(Room_Type_Code))
go
Data in the Table.
1011AvailSTD1
1012AvailSTD1
2011AvailDLX2
Create table Room_Bookings
(Booking_ID char(8) NOT NULL ,
Room_no char(4) NOT NULL Foreign KEY REFERENCES Rooms,
Adult_Num int ,
Child_Num int,
Chck_in_Date date,
Chck_out_Date date,
Rack_Rate decimal(7,2),
Guest_ID char(13)
)
go
Booking_ID R_No Arrv_Date Dep_Date
083044124012 2012-08-15 2012-08-25750.009210021234567
083744124012 2012-08-20 2012-09-28750.009210021234567
0855412 1012 2012-08-15 2012-09-30450.009201112345678
123456781012 2012-08-22 2012-08-23450.000987654321123
123456781012 2012-08-25 2012-09-23450.000987654321123
The query does nt return correct room_Nos if the Arrv_Date and Dep_date are within the range of booked dates.
e.g Arrv_Date:2012-08-22 ,Dep_date:2012-09-20 ,it will return room 4012 and 1012 ofwhich they are booked by tht date.
August 26, 2012 at 1:11 pm
Please don't hijack other people's threads. You've already created 3 for this problem.
Any replies to mfundotogu please direct to http://www.sqlservercentral.com/Forums/Topic1350128-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply