I have this stored procedure. What it is basicly trying to do is get a list of all blocks (meetings.isblock=1) and the date to be released (meetings.endtime-meetings.releasedays), and the Highest date/time that belongs to that block (meetings.blockmember=1 and blockid=meetings.blockid).

The query is getting all the right data, and when I was just updating the iscompleted or Endtime field it worked great. Then I added the case statement to the update statement to update the originalBlockStart and OriginalBlockEnd. I put these in a case, because I don't want to update them if they already have a valid date/time entry.

So anyway, I've two problems going on my test data of two records.

meeting   BT               ET              OrigBlkSt  OrigBlkEnd
------------------------------------------------------------------
11364  04/09/2002 09:00  04/09/2002 12:00   NULL        NULL
11366  04/09/2002 09:00  04/09/2002 12:00   NULL        NULL

* Abbrivated field names

When I run the procedure, I get the following messages in the QA

Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)


(1 row(s) affected)

And the data now has the following:

meeting   BT               ET              OrigBlkSt  OrigBlkEnd
------------------------------------------------------------------
11364  04/09/2002 09:00  NULL               NULL        NULL
11366  04/09/2002 09:00  04/09/2002 09:30   NULL        NULL

* Abbrivated field names

Meeting # 11364 has no child records, it should have the iscancelled flag changed to 1 and the Original fields with data.
Meeting # 11366 has 1 child record, it should have the EndingTime changed to the LastCaseEnds value, and it does. But the Original fields are not getting updated:


Here is the code:

DECLARE releaseTable CURSOR FOR 
select  m.meetingnumber,m.begintime,m.endtime,m.begintime-m.releasedays as ReleaseDate,m.OriginalBlockStart,m.OriginalBlockEnd,
(select max(s.Endtime) from meetings s 
              where s.blockmember=1 and s.iscancelled=0
              and s.blockid=m.blockid) as lastCaseEnds
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 @beginTime datetime
declare @endTime datetime
declare @releaseDate datetime
declare @originalBlockStart datetime
declare @originalBlockEnd datetime
declare @lastCaseEnds datetime

FETCH NEXT FROM releaseTable into @meetingnumber,@beginTime,@endtime,@releasedate,@OriginalBlockStart,@OriginalBlockEnd,@lastCaseEnds
WHILE (@@FETCH_Status=0) BEGIN
     IF (@LastCaseEnds = NULL)  BEGIN   -- If lastCaseEnds=NULL there are no children records associated with this block
	UPDATE dbo.meetings set iscancelled=1,
                                OriginalBlockStart=@BeginTime,
                                OriginalBlockEnd=@EndTime
        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
        UPDATE dbo.meetings set endtime = @LastCaseEnds, 
                                OriginalBlockStart=case WHEN @OriginalBlockStart=NULL THEN @begintime ELSE @OriginalBlockStart END,
                                OriginalBlockEnd  =case WHEN @OriginalBlockEnd=NULL then @BeginTime Else @OriginalBlockEnd END
        WHERE dbo.meetings.meetingnumber = @meetingnumber
     END
     FETCH NEXT FROM releaseTable into @meetingnumber,@beginTime,@endtime,@releasedate,@OriginalBlockStart,@OriginalBlockEnd,@lastCaseEnds
END

Close releaseTable

DEALLOCATE releaseTable