SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Function / Stored Procedure to update field value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/12/2013 :  16:12:00  Show Profile  Reply with Quote
Hi,

I'm already sorry ... I am quite new to this whole posting stuff, but I am trying my best ...

I got two tables ... 'Customer' and 'Rooms' ... a single Customer with a UNIQUE CU_ID is staying in a room (with a UNIQUE ROOM_ID, unfortunately for me, sometimes there are 2 or three CU_ID staying in the same Room ... they usually check in at the same day though, so the CheckInDate should be the same ...

When they check in and the RoomNo is entered on the 'Customer'-Table I would like the Bit-Field 'Occupied' in the 'Rooms'-Table to be set to "TRUE". That part I got done with a Trigger (see below) ...

The trick is when they are checking out ... If a User manually marks the "Occupied"-(Bit)Field for that Room-ID as "FALSE", then I would like to set the DepartDate on the Customer-Table for ANY Customer staying in that room at the MOMENT to Getdate().

Here's my tables, Trigger, and some test data:


CREATE TABLE [dbo].[Rooms](
	[Room_ID] [int] IDENTITY(1,1) NOT NULL,
	[Room_No] [nvarchar](50) NULL,
	[Occupied] [bit] NULL,
        [CheckInDate] [int] NULL,

CONSTRAINT [PK_Rooms] PRIMARY KEY CLUSTERED 
(
	[Room_ID] 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

CREATE TABLE [dbo].[Customer](
	[CU_ID] [int] IDENTITY(5000,1) NOT NULL,
	[CheckInDate] [datetime] NULL,
	[RoomNo] [int] NOT NULL,
	[Nights_Booked] [int] NULL,
	[DepartDate] [datetime] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CU_ID] 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

ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Rooms] FOREIGN KEY([RoomNo])
REFERENCES [dbo].[Rooms] ([Room_ID])
GO

ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Rooms]
GO

-- 2 Tables created including PK/FK Relationship



Here's my Trigger for the first step ... updating the bit column Occupied to True when Room_ID is used for new Check-In:


Create TRIGGER [dbo].[Occupied] 
ON [dbo].[Customer]
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
	IF TRIGGER_NESTLEVEL() > 1
    RETURN
	UPDATE Rooms
	SET [Occupied] = 'True'
	FROM  Rooms r
	JOIN Customer cu
	ON cu.[RoomNo] = r.[Room_ID]
	Join INSERTED INS
	ON cu.[RoomNo] = INS.[RoomNo]
END
GO


I enter some test data into both of them ...


SET IDENTITY_INSERT Rooms ON

INSERT INTO Rooms
(Room_ID, Room_No, Occupied) 
SELECT 1, 'A14', 0 UNION ALL
SELECT 2, 'B2', 0 UNION ALL
SELECT 3, 'C3', 0 UNION ALL
SELECT 4, 'D8', 0 UNION ALL
SELECT 5, 'K9', 0 

SET IDENTITY_INSERT Rooms OFF

GO

SET IDENTITY_INSERT Customer ON

INSERT INTO Customer
(CU_ID, CheckInDate, RoomNo, Nights_Booked, DepartDate) 
SELECT 5000, '2013-05-10', 1, 4, NULL UNION ALL
SELECT 5001, '2013-05-10', 1, 4, NULL UNION ALL
SELECT 5002, '2013-05-10', 2, 2, NULL UNION ALL
SELECT 5003, '2013-05-10', 3, 3, NULL UNION ALL
SELECT 5004, '2013-05-11', 4, 4, NULL UNION ALL
SELECT 5005, '2013-05-11', 4, 4, NULL UNION ALL
SELECT 5006, '2013-05-11', 4, 4, NULL

SET IDENTITY_INSERT Customer OFF

-- Test Data entered in rows on 'Rooms' and 'Customer'-Tables


The Trigger works fine and it updates all the Records with the same Room_ID (RoomNo respectively on Customer Table).

I tried to solve my problem with other Triggers. And I get SQL Server to enter the Depart-Date based on the Check-In-Date of the specific Customer, if I pass that one on to the Room-Table. Unfortunately it only updates the Data with the 1st Entry made for that specific Room_ID on the Rooms-Table ... and it seems awkwardly much passing back and forth between the two tables. I guess I need a Stored Procedure/Function to actually accomplish that:

- On Insert of Customer Record pass NEWEST CheckInDate and Insert into Room-Table Field CheckInDate
- When Rooms.Occupied is marked as 'False', set the Check-Out-Date for all CU_ID with Customer.RoomNo = Rooms.Room_ID AND Customer.CheckInDate = Rooms.CheckInDate to GETDATE() ...

I struggle with the first part - how to pass the CheckInDate on Insert and if a value is existing update it with the newer date ...

No idea, again ... I'm all new :)

Thanks for any help in advance !!!

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/13/2013 :  00:36:20  Show Profile  Reply with Quote

Create TRIGGER [dbo].[Occupied] 
ON [dbo].[Customer]
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
   UPDATE r
   SET r.CheckInDate =
   FROM Room r
   INNER JOIN (SELECT RoomNo,MAX(CheckInDate) AS MaxDate
               FROM Customer
               GROUP BY RomNo
              )c
   ON c.RoomNo = r.Room_ID
END
GO


CREATE TRIGGER [CheckOut]
ON Rooms
FOR UPDATE
AS
BEGIN
   UPDATE c
   SET c.CheckOutDate= GETDATE()
   FROM Customer c
   INNER JOIN INSERTED i
   ON i.Room_ID = c.RoomNo 
   AND i.CheckInDate = c.CheckInDate 
   
END


between i dont see column CheckOutDate in Customer so i guess you may have to add it before you do the above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/14/2013 :  12:43:13  Show Profile  Reply with Quote
quote:

between i dont see column CheckOutDate in Customer so i guess you may have to add it before you do the above


Thanks a lot for your reply and solution, Visakh ...

CheckOutDate is actually named DepartDate, sorry I wasn't clear on that one ....

I found a different solution in the meantime, by passing on the CU_ID (better than CheckInDate, as there may be multiple Check-In for the same Room on the same day in crazy busy times) and just replacing the Inserted I was using with that CU_ID ...

Your option seems a lot more simple though and I'd definitely give it a try - thanks again:)

Now I got a a different problem with a Stored Procedure that's updating this field automatically all the times ... but I'd post that separate from this post, as this one is awkwardly long already thanks to me :)

Edited by - carstentao on 05/14/2013 12:48:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  00:29:30  Show Profile  Reply with Quote
you're welcome
Glad that you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000