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.
| Author |
Topic |
|
ashwika
Starting Member
9 Posts |
 Posted - 2011-07-17 : 03:41:16
|
| I want to write a stored procedure that reads 2 tables and sets ‘Status’ on Table 1 to ‘C’ if request is complete .Please advise – I am new to Sql programming . …..Thank you I have 2 tables Requests & Bookings (Table 1 and Table 2) For each request for a room there are many booking records . Read each record in Table 1 and corresponding records in Table 2..If ToDateTime = system date or ToDateTime > systemdate on Table2 then Status needs to be changed to ‘C’ on Table 1 . Table 1 RequestID 005RequestDate 1/1/2011Name SmithEmail smth@abc.hotmail.comTel 01552366662RoomNo 12Location T1Status NC etc etc--------------------------------------------------------------------------------------------------------Table 2 BookingID 001 RequestID 005 RequestDate 1/1/2011 FromDateTime 1/1/2011 09.00ToDateTime 1/1/2011 10.00BookedBy Smith BookingID 002 RequestID 005 RequestDate 11/1/2011 FromDateTime 12/1/2011 09.00ToDateTime 12/1/2011 13.00BookedBy Smith BookingID 003 RequestID 005 RequestDate 1/1/2011 FromDateTime 13/1/2011 09.00ToDateTime 13/1/2011 10.00bookedBy Smith BookingID 004 RequestID 005RequestDate 14/1/2011 FromDateTime 15/1/2011 09.00ToDateTime 15/1/2011 10.00BookedBy Smith If ToDateTime = system date or ToDateTime > systemdate then status needs to be changed to ‘C’ on Table 1 . Thank you |
|
|
ashwika
Starting Member
9 Posts |
Posted - 2011-07-17 : 03:46:48
|
| I want to write a stored procedure that reads 2 tables and sets ‘Status’ on Table 1 to ‘C’ if request is complete .Please advise – I am new to Sql programming . …..I have 2 tables Requests & Bookings (Table 1 and Table 2) For each request for a room there are many booking records . Read each record in Table 1 and corresponding records in Table 2..If ToDateTime = system date or ToDateTime > systemdate on Table2 then Status needs to be changed to ‘C’ on Table 1 . Table 1 RequestID 005RequestDate 1/1/2011Name SmithEmail smth@abc.hotmail.comTel 01552366662RoomNo 12Location T1Status NC etc etc--------------------------------------------------------------------------------------------------------Table 2 BookingID 001 RequestID 005 RequestDate 1/1/2011 FromDateTime 1/1/2011 09.00ToDateTime 1/1/2011 10.00BookedBy Smith BookingID 002 RequestID 005 RequestDate 11/1/2011 FromDateTime 12/1/2011 09.00ToDateTime 12/1/2011 13.00BookedBy Smith BookingID 003 RequestID 005 RequestDate 1/1/2011 FromDateTime 13/1/2011 09.00ToDateTime 13/1/2011 10.00BookedBy Smith BookingID 004 RequestID 005RequestDate 14/1/2011 FromDateTime 15/1/2011 09.00ToDateTime 15/1/2011 10.00BookedBy Smith If ToDateTime = system date or ToDateTime > systemdate then status needs to be changed to ‘C’ on Table 1 . |
 |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-07-18 : 08:11:25
|
| are you going to schedule it as a job for this? |
|
|
|
|
|
|
read 2 tables in SQL - if condition met