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
ALTER TABLE [dbo].[RoomRate] ADD CONSTRAINT [DF_RoomRate_roomRate] DEFAULT ((0)) FOR [roomRate]
GO
ALTER TABLE [dbo].[RoomRate] ADD CONSTRAINT [DF_RoomRate_roomRateState] DEFAULT ((1)) FOR [roomRateState]
GO
What I have tried:
how to get rooms available