I dont know if I fully understand what you are doing. Are you trying keep the sysorder field in order, even if one gets deleted? If the first one gets deleted do you want the others to trickle up into the open position?


>The code below is run for each month, so I have 12 of these. Each case is recorded in the meetings table, and for each procedure they are doing on a case there is a child record with the procedure code and coPerID. So if I have a case that has two procedures and two different doctors, I will have one parent record in the meetings table and 2 child records in the meetproc table. For reporting purposes the surgeon listed first in the meetproc table gets credit for the procedure. There is a field called sysorder that counts up from 1 for each record in the meetproc table. So 1 would be the first record, 2 would be on the second record. But if they remove procedures the counter keeps going up. So they might delete the first procedure and doctor, and add another, that one will get a sysorder of 3. Now I don't have a sysorder with a value of one. What I'm trying to get is a case count, and not a procedure count where the meetproc.sysorder value is the min() value...whatever that
>value may be and the coPerID (personnel ID) matches the one I'm running the query for at the time (this code is in a loop that goes through all doctors who have done a case). The code below gives credit even if they are not the first record (by sysorder) in the meetproc table.
>
>

>		-- Jul Current Year
>		declare cur_Month7 Cursor For
>			(select count(det.count1) as Mon7
>			from
>			(select  sum(1) as count1
>			   from meetings inner join meetproc on meetings.meetingnumber=meetproc.meetingnumber
>				where (datepart(mm,meetings.begintime)=7)
>				and meetproc.coperID=@coPerID
>				and datepart(yyyy,meetings.begintime)=@tiYear
>				and meetings.deptID=@tiDept
>				and meetings.iscompleted=1
>				and meetings.iscancelled=0
>				group by meetings.meetingnumber) det)
>

>
>Thanks for any help.
>
>Kirk