/****** Object: Stored Procedure dbo.Assign_Meeting_Date Script Date: 11/15/2004 6:54:20 AM ******//****** Object: Stored Procedure dbo.sp_Assign_Meeting_Date Script Date: 10/1/2004 12:50:40 PM ******//****** Object: Stored Procedure dbo.Assign_Meeting_Date Script Date: 11/26/2003 1:01:57 PM ******/CREATE procedure Assign_Meeting_Date @c_SID nvarchar(10)ASdeclare @start_of_nextweek datetime, @priority_sought int, @slot datetime Declare @p_stat nvarchar(5), @c_stat nvarchar(5), @comment nvarchar(255), @now datetime, @start_of_nextweek_plus_6 datetime, @start_of_nextweek_plus_12 datetime, @maxPriority int, @filld int, @rcount int --set @start_of_nextweek= CONVERT(CHAR(10),GETDATE(),110) set @start_of_nextweek= getdate() select @filld = (select filled from buckets where bucket_time = '3/2/2007 12:30:00 PM') -- If the disk is uploaded on any day after Thursday then, -- move to the coming Monday and then start finding a slot -- as before. while ( datename(weekday,@start_of_nextweek) > 'Friday' and datename(weekday,@start_of_nextweek) < 'Monday') begin set @start_of_nextweek=dateadd(day,1,@start_of_nextweek) if( datename (weekday, @start_of_nextweek) = 'Monday' ) break end -- Catering for the fact that Disk could be uploaded on latest by Wednesday -- and still being able to assign a meeting date of Tuesday next week at the -- earliest set @priority_sought=1 if( datename (weekday, @start_of_nextweek) = 'Tuesday' ) set @start_of_nextweek = @start_of_nextweek -1 else if ( datename (weekday, @start_of_nextweek) ='Wednesday' ) set @start_of_nextweek = @start_of_nextweek - 2 else if ( datename (weekday, @start_of_nextweek) ='Thursday' ) set @start_of_nextweek = @start_of_nextweek - 3 -- If the disk is uploaded uptil Thursday you can invite people for the coming Tuesday else if ( datename (weekday, @start_of_nextweek) ='Friday' ) set @priority_sought = 2 while (1=1) begin set @start_of_nextweek_plus_6 = @start_of_nextweek + 6 set @start_of_nextweek_plus_12 = @start_of_nextweek + 12 while (1=1) begin select @slot=null set @slot=( select b.bucket_time from (SELECT Meeting_Day, Capacity, Filled, Priority, CONVERT(datetime, CONVERT(char, Bucket_Time, 1), 1) AS Bucket_Time, Attended FROM Buckets) b where (b.capacity>b.filled) and (b.priority=@priority_sought) and Bucket_Time between @start_of_nextweek_plus_6 AND @start_of_nextweek_plus_12 ) if (@slot is not null) begin break end select @maxPriority = (select max(priority) from buckets) if @priority_sought=@maxPriority break set @priority_sought=@priority_sought+1 end if @slot is not null break -- if you were not able to find a time slot in a period of between 6 to 12 days from the -- current day ( which could be Monday if you uploaded the disk on Monday uptill Thursday, -- or Friday if you were uploading the disk on a Friday) while ( @priority_sought=@maxPriority and (@slot is null) ) begin if(datename(weekday,@start_of_nextweek)<>'Monday') begin set @start_of_nextweek=dateadd(day,1,@start_of_nextweek) end if( datename (weekday, @start_of_nextweek) = 'Monday' ) begin set @priority_sought = 1 break end end -- end of while( @priority_sought=@maxPriority and (@slot is null) ) loop if @start_of_nextweek >(select max(Bucket_time) from buckets) break end -- end of while(1) loop select @slot = (select bucket_time from Buckets where priority = @priority_sought and DATEPART(DAY,bucket_time) = DATEPART(DAY,@slot) and DATEPART(MONTH,bucket_time) = DATEPART(MONTH,@slot) and DATEPART(YEAR,bucket_time) = DATEPART(YEAR,@slot) ) update dockets set Meeting_date=@slot from dockets where dockets.student_ID = @c_SID set @rcount = @@rowcount if (@rcount = 1) begin set @filld = (select filled from buckets where bucket_time = @slot) update buckets set filled = filled + 1 where bucket_time=@slot set @filld = 0 set @filld = (select filled from buckets where bucket_time = @slot) set @filld = 0 endGO