I'm hoping that someone can review this stored procedure. I think it works right...but I'm not a 100% sure. This procedure is ran by SQL Agent as a job at a timed interval (normally once an hour). I have cases scheduled that are blocks. This procedure is supposed to check to see if the sum minutes begintime - endtime of cases assoicated with the block are less than the total block minutes (begintime-endtime of the block record). If there is excess time (block time greater than sum of the assoicated cases), then I either cancel the block (if no cases assoicated at all) or I change the ending time of the block to the ending time of the latest associated case. The key is that the release happens x days in advance of the scheduled block. When a block is defined, the user sets the number of days in advance that the block will be released. Example: a block is scheduled on wed and autorelease days is 2 then on monday the system should release any unused time in the block. My concern is I have a block that was released on 11/01/2003 that had a begin time of 03-09-2004 07:30 and an end time of 03-09-2004 15:00 with the autorelease days set at 2 so it shouldn't have released until 03-07-2004. I'm worried I may be doing the date calculations wrong.

Thanks for any comments and help.

CREATE PROCEDURE job_AutoReleaseBlocks 

AS

DECLARE releaseTable CURSOR FOR 
select  m.meetingnumber,m.endtime,m.begintime-m.releasedays as ReleaseDate,
(select max(s.Endtime) from meetings s 
              where s.blockmember=1 and s.iscancelled=0
              and s.blockid=m.blockid) as lastCaseEnds,
dateDiff(mi,m.OriginalBlockStart,m.OriginalBlockEnd) as OrigBlockMins,
isnull((select sum(datediff(mi,k.begintime,k.endtime))
	from meetings k
        where k.blockmember=1 and k.iscancelled=0
        and k.blockid=m.blockid),0) as ScheduledMins
from meetings m
where isblock=1 and isnull(releaseDays,0)>0
and datepart(dd,begintime-releasedays)=datepart(dd,getdate())
and datepart(mm,begintime-releasedays)=datepart(mm,getdate())
and datepart(yy,begintime-releasedays)=datepart(yy,getdate())
and m.iscancelled=0

open releaseTable

declare @meetingnumber int
declare @endtime datetime
declare @releaseDate datetime
declare @lastCaseEnds datetime
declare @origBlockMins int
declare @ScheduledMins int
declare @BlockUtilization int

FETCH NEXT FROM releaseTable into @meetingnumber,@endtime,@releasedate,@lastCaseEnds,@origBlockMins,@ScheduledMins
WHILE (@@FETCH_Status=0) BEGIN
     SET @BlockUtilization=((@scheduledMins/@OrigBlockMins)*100)
     IF (@LastCaseEnds = NULL)  BEGIN   
        -- If lastCaseEnds=NULL there are no children records associated with this block
	UPDATE dbo.meetings set iscancelled=1, 
            blockUtilization=0, AutoReleaseDT=GetDate() 
            where dbo.meetings.meetingnumber = @meetingnumber
	
     END ELSE BEGIN 
        -- Adjust the end time of the meetings table to 
        -- match the end time of the last scheduled case for that block
	IF @LastCaseEnds <= @EndTime   BEGIN-- Adjust Block End time to Meet Last Case Time
           UPDATE dbo.meetings set endtime= @LastCaseEnds, 
                 blockUtilization=@BlockUtilization,
                 AutoReleaseDT=GetDate() where dbo.meetings.meetingnumber = @meetingnumber
	   
	 END ELSE
	   BEGIN 
                UPDATE dbo.meetings set blockUtilization=@blockutilization,
                   autoReleaseDT=getDate() where dbo.meetings.meetingnumber=@meetingnumber
           END    
     END
     FETCH NEXT FROM releaseTable into @meetingnumber,@endtime,@releasedate,@lastCaseEnds,@origBlockMins,@ScheduledMins
END
Close releaseTable
DEALLOCATE releaseTable