How to get rooms available

Question

1.44/5 (2 votes)

See more:

help me
The sql statement obtains the free space in the attached table, the roomAvailable field is the number of available rooms that day, the sql statement must now check for room availability from date to date.

this is structure table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoomRate](
	[roomRateID] [int] IDENTITY(1,1) NOT NULL,
	[roomID] [int] NOT NULL,
	[roomRate] [float] NOT NULL,
	[roomAvailable] [int] NOT NULL,
	[roomRateDate] [date] NOT NULL,
	[roomRateState] [bit] NOT NULL,
 CONSTRAINT [PK_RoomRate] PRIMARY KEY CLUSTERED 
(
	[roomRateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[RoomRate] ON
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (1, 55, 100, 4, CAST(0xCC3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (2, 55, 100, 3, CAST(0xCD3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (3, 55, 100, 1, CAST(0xCE3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (4, 55, 100, 0, CAST(0xCF3D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (5, 55, 100, 3, CAST(0xD03D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (6, 55, 100, 0, CAST(0xD13D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (7, 55, 100, 5, CAST(0xD23D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (8, 55, 100, 3, CAST(0xD33D0B00 AS Date), 1)
INSERT [dbo].[RoomRate] ([roomRateID], [roomID], [roomRate], [roomAvailable], [roomRateDate], [roomRateState]) VALUES (9, 55, 100, 2, CAST(0xD43D0B00 AS Date), 1)
SET IDENTITY_INSERT [dbo].[RoomRate] OFF
/****** Object:  Default [DF_RoomRate_roomRate]    Script Date: 01/25/2018 20:05:29 ******/
ALTER TABLE [dbo].[RoomRate] ADD  CONSTRAINT [DF_RoomRate_roomRate]  DEFAULT ((0)) FOR [roomRate]
GO
/****** Object:  Default [DF_RoomRate_roomRateState]    Script Date: 01/25/2018 20:05:29 ******/
ALTER TABLE [dbo].[RoomRate] ADD  CONSTRAINT [DF_RoomRate_roomRateState]  DEFAULT ((1)) FOR [roomRateState]
GO

What I have tried:

how to get rooms available

Posted 25-Jan-18 15:40pm

Member 12400712

Updated 4-Feb-18 8:44am

Add a Solution

Comments

nitrous_007 25-Jan-18 23:25pm

Change the title of your question to something more relevant. Rooms is something only relevant to you. If somebody sees the title of the question, they would have no idea what you are talking about.

Rajesh Pandya 26-Jan-18 7:14am

You have given only table structure. What query you have tried? What error you have get? Please specify that.

ZurdoDev 26-Jan-18 10:00am

Where are you stuck?

CHill60 5-Feb-18 7:59am

On a separate note - why create an identity column and then supply the roomRateID yourself? Pointless.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-02-04T08:41:00

Solution 1

It's quite obvious... You have to use WHERE clause[^]:

SQL

SELECT *
FROM RoomRate
WHERE roomRateDate BETWEEN '2018-01-01' AND '2018-01-31'

For further details, please see:
SELECT Examples (Transact-SQL) | Microsoft Docs[^]

Posted 4-Feb-18 8:41am

Maciej Los