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 |
|
awalker
Starting Member
12 Posts |
 Posted - 2012-07-18 : 15:25:10
|
| Hi AllI hope I can explain what I need.I have a table called Appointments with 2 columns: AppointmentDate, AppointmentTime.I have a second table called AppointmentTimes with 3 columns: DayOfWeekName, StartTime, EndTime.What I need to do is create a stored procedure that takes a date parameter and does something like this:if not exists (select 1 from Appointments where AppointmentDate = @date) { if it does not exist then I need to use the @date to get the day of the week. Then using the day of the week I would get the StartTime and EndTime from the AppointmentTimes table. Then insert a new row for each hour into the Appointments table starting at the StartTime until the EndTime. }in the end the Appointments table data would be something like this:AppointmentDate = 07/18/2012, AppointmentTime = 10:00:00AppointmentDate = 07/18/2012, AppointmentTime = 11:00:00AppointmentDate = 07/18/2012, AppointmentTime = 12:00:00AppointmentDate = 07/18/2012, AppointmentTime = 13:00:00 ect.... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-18 : 15:31:00
|
| Original:edit: the CORRECT link this time: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176899There's no need to create a row in another table, all of the data you're requesting can be derived from the original appointment. Nigel's solution in the original thread should suffice. Have you tested it? |
 |
|
|
awalker
Starting Member
12 Posts |
Posted - 2012-07-18 : 15:44:59
|
| I did see his solution, but was not able to understand it. Let me see if I can clarify a little what I have going on here. The Appointments table also has a column called IsAvailable. So what I need is when a user clicks on a certain day, if they are the first to click on it I would take that date, get the starttime and endtime (from the other table) for that day of the week and create a list of appointment times that will be displayed back to the user. If they are not the first to click on that day, the list will already exist so it will just return the list. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-07-18 : 16:09:42
|
| It would be a lot easier if you posted the DDL for all the affected tables, DML statements for sample data, and the expected output. See here for examples:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
awalker
Starting Member
12 Posts |
Posted - 2012-07-18 : 16:40:51
|
| I hope this is what you are looking forDDL of tables:CREATE TABLE Appointments(id int, AppointmentDate datetime, AppointmentTime time, IsAvailable bit)CREATE TABLE AppointmentTimes(DayOfWeekName string, StartTime time, EndTime time)DMLINSERT INTO AppointmentTimes values ('Sunday', '10:00:00', '17:00:00'),('Monday', '10:00:00', '17:00:00'),('Tuesday', '10:00:00', '17:00:00'),('Wednesday', '10:00:00', '17:00:00'),('Thursday', '10:00:00', '17:00:00'),('Friday', '10:00:00', '17:00:00'),('Saturday', '10:00:00', '17:00:00')my expected output, if it was the first time a particular day was selected would be something like this, a new row would be create based on the StartTime and EndTime from the other table:INSERT INTO Appointments(AppointmentDate, AppointmentTime, IsAvailable) values ('07/18/2012', '10:00:00', 'true'),('07/18/2012', '11:00:00', 'true'),('07/18/2012', '12:00:00', 'true'),('07/18/2012', '13:00:00', 'true'),('07/18/2012', '14:00:00', 'true'),('07/18/2012', '15:00:00', 'true'),('07/18/2012', '16:00:00', 'true'),('07/18/2012', '17:00:00', 'true') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
More help with Stored Procedure