Automatic Records Delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Automatic Records Delete

Author

Topic

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:27:39

Hotel Reservation System

How to delete Automatically one or more records based from my computer's time?

For example :
RoomNumber : 10
ReservedTime : 10:00am - 4:00pm

When my computer's time reached at 4:00pm, RoomNumber 10 will not be Reserved anymore if no clients came.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 10:46:53

thats just a matter of simple update script. i dont think this needs to be deleted physically. rather it should be logical deletion or update which will change status from Occupied to Vacant or similar values. this updation should happen when customer does check out action.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 11:24:09

Yes it will does when the client checks out, but then.. i want to it to be deleted when the client did'nt came to check in by that time.

Vonn Liquiran

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 11:31:46

why deleted? what if you want to check previous status of room at later stage? or is there separate history/audit table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 11:57:31

Im sorry, I want to update the RoomStatus automatically not delete :)

Vonn Liquiran

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 12:05:41

Hello Vonn ,

If your table structure is given here; http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165638 - Then you can use similar logic as posted there;


UPDATE Reservation
SET [status] = <your new status>
WHERE GETDATE() >= dateadd(hh,hoursSpan,dateadd(mi, datepart(mi,arrivalTime), dateadd(hh,datepart(hh,arrivalTime),arrivalDate)))

We compare the current datetime [GETDATE()] to the calculated departure datetime. If the current datetime >= that datetime then the status will be updated.

You can see in the few threads you have here, that we are often performing calculations on the two fields (ArrivalTime and HoursSpan). We are constantly reassembling these to perform compares. If you are in an A&D phase of this system (as you suggested), if might be worth it to consider storing the values as native datetime rather than seperating them into a date and then a time. So you end up with columns which are native (i.e. ArrivalDateTime and DepartureDateTime). Storing these as native datetime values will reduce coding, improve filtering, and improve query execution by making filters sargable.

Some options would be to format the date and time in the client application before they are persisted to the DB native datetime datatypes, you could create computed columns in Reservation table, or you could use a UDF to replace the continual date functions to reassemble date and time.

Here is a simple udf to perform the conversion;

CREATE FUNCTION dbo.udfConvertDateTime (@Date smalldatetime, @Time smalldatetime, @Hours tinyint)
RETURNS smalldatetime
AS
BEGIN
	DECLARE @result smalldatetime
	SELECT @result = dateadd(hh,@Hours,dateadd(mi, datepart(mi,@Time), dateadd(hh,datepart(hh,@Time),@Date)))
	RETURN (@result)
END
GO

SELECT 
	dbo.udfConvertDateTime('09-20-11','10:00am',0) AS ArrivalDateTime,
	dbo.udfConvertDateTime('09-20-11','10:00am',6) AS DepartureDateTime

DROP FUNCTION dbo.udfConvertDateTime

Just some thoughts for your consideration.

HTH.

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-21 : 01:52:30

I dont get the last part of your answer? You said that

"If the current datetime >= that datetime then the status will be updated."

That is what I'm thinking, How can I do that? I mean automatically be changed when the dateSpan is already finished.

I am using VB 2010 and SQL Server 2008.

Vonn Liquiran

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 05:51:05

why not do this as a part of checkout action?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-21 : 06:24:31

Can you follow this thread?
http://stackoverflow.com/questions/7496728/time-spans-for-sql-server

All I want is to retrieve all available rooms in my database

Vonn Liquiran

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 07:09:27

Something like


SELECT rt.*
FROM [ROOM TABLE] rt
LEFT JOIN [RESERVATION TABLE] rst
ON rst.RoomNo = rt.RoomNo
AND rst.DepartureDate < GETDATE()
WHERE rst.roomNo IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-21 : 07:37:05

What do you mean by rt and rst?

Vonn Liquiran

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-21 : 08:24:36

I guess I figured it out! Whew!

SELECT Booking.BookingID, Booking.ReservationNo, Booking.RoomNo, RoomStatus.RoomStatus, Booking.ArrivalDate, Booking.DepartureDate
FROM Booking INNER JOIN
RoomStatus ON Booking.RoomStatusId = RoomStatus.RoomStatusId
WHERE (ArrivalDate NOT BETWEEN '06:00:00' AND '12:00:00') OR
(DepartureDate NOT BETWEEN '06:00:00' AND '12:00:00')

If that query returns nothing, then I can place a reservation for a certain RoomNo :)

Thanks for your ideas! :)

Vonn Liquiran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources