April 27, 2010 at 9:57 am
I've table as follows,
CREATE TABLE [dbo].[roomBooked](
[idx] [int] IDENTITY(1,1) NOT NULL,
[roomno] [varchar](50) NOT NULL,
[useStrDte] [datetime] NOT NULL,
[useEndDte] [datetime] NOT NULL,
[dtVersion] [timestamp] NOT NULL,
CONSTRAINT [PK_roomBooked] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Above table storing booking room no, Start Date Used, and End Date Used.
My scenario as follow,
1. If roomno='2A', useStrDte=2 May 2010, and useEndDte=7 May 2010, insert statement as follow
insert into roomBooked(roomno,useStrDte,useEndDte) values('2A','5/2/2010','5/7/2010');
2. Let's say, there's another transaction trying to execute as follow
insert into roomBooked(roomno,useStrDte,useEndDte) values('2A','5/6/2010','5/9/2010');. The fact is, this T-SQL have to be prevented because of roomno='2A' is booked from 2 May 2010 till 7 May 2010.
My question is,
1. How T-SQL look's like to prevent insert into roomBooked(roomno,useStrDte,useEndDte) values('2A','5/6/2010','5/9/2010'); to be inserted.
April 27, 2010 at 11:18 am
You could use a unique index or constraint.
April 27, 2010 at 11:22 am
awesome that you posted the table structure and sample data; it made my creation of a possible solution and testing so easy. thank you!!!
The only way i see is you have to check all the dates between the current bookings; to do that, you need some sort of a Calendar table.
note i changed the way the insert occurs in my example. I hope it's obvious you'd parameterize the three values.
this example seems to work just as you'd expect; here's the PRINT results:
no overlap, proceed with insert
(1 row(s) affected)
Raise An Error, date overlap.
and here's the code with a calendar table:
--drop table TallyCalendar
IF object_id('TallyCalendar') IS NOT NULL
DROP Table dbo.TallyCalendar
GO
CREATE TABLE dbo.TallyCalendar (
TheDate datetime not null primary key,
[DayOfWeek] varchar(50),
IsHoliday bit default 0,
IsWorkHoliday bit default 0,
HolidayName varchar(100) )
CREATE NONCLUSTERED INDEX [IX_TallyCalendar]
ON [dbo].[TallyCalendar] ([DayOfWeek],[TheDate])
INCLUDE ([IsHoliday],[IsWorkHoliday],[HolidayName])
Declare @NumberOfYears int
SET @NumberOfYears = 50 --x years before and after todays date:
;WITH TallyNumbers AS
(
SELECT dateadd( dd,(-365 * @NumberOfYears) + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
FROM (
SELECT TOP (730 * @NumberOfYears)
row_number() OVER (ORDER BY sc1.id) AS RW
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
) X
)
INSERT INTO dbo.TallyCalendar(TheDate,[DayOfWeek])
SELECT
TallyNumbers.N,
datename(dw,TallyNumbers.N)
FROM TallyNumbers
CREATE TABLE [dbo].[roomBooked](
[idx] [int] IDENTITY(1,1) NOT NULL,
[roomno] [varchar](50) NOT NULL,
[useStrDte] [datetime] NOT NULL,
[useEndDte] [datetime] NOT NULL,
[dtVersion] [timestamp] NOT NULL,
CONSTRAINT [PK_roomBooked] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
declare @room varchar(10),
@st datetime,
@et datetime
SELECT @room = '2A',
@st = '5/2/2010',
@et = '5/7/2010'
if exists(select
roomBooked.*,
TallyCalendar.TheDate
from roomBooked
inner join TallyCalendar on TallyCalendar.TheDate between roomBooked.useStrDte and roomBooked.useEndDte
WHERE roomBooked.roomno = @room
AND TallyCalendar.TheDate between @st and @et )
begin
PRINT 'Raise An Error, date overlap.'
end
else
begin
PRINT ' no overlap, proceed with insert'
insert into roomBooked(roomno,useStrDte,useEndDte)
SELECT @room,@st,@et
end
--next date to test:
SELECT @room = '2A',
@st = '5/6/2010',
@et = '5/9/2010'
if exists(select
roomBooked.*,
TallyCalendar.TheDate
from roomBooked
inner join TallyCalendar on TallyCalendar.TheDate between roomBooked.useStrDte and roomBooked.useEndDte
WHERE roomBooked.roomno = @room
AND TallyCalendar.TheDate between @st and @et )
begin
PRINT 'Raise An Error, date overlap.'
end
else
begin
PRINT ' no overlap, proceed with insert'
insert into roomBooked(roomno,useStrDte,useEndDte)
SELECT @room,@st,@et
end
Lowell
April 27, 2010 at 11:34 am
not sure how you handle checking in on hte same day someone else checks out; if i leave on 05/07/2009, someone else presumably could check in on the same date, right? you might need to tweak the logic just a bit to take that into consideration.
Lowell
April 27, 2010 at 6:21 pm
Hello Sir,
tq very much. your guidance is my inspiration
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply