Bob: Try this.
Create a temporary table with the appropriate columns (doctor, date, time, procedure, etc.) Insert data into this temporary table ordering by doctor, and date descending order. Declare a cursor using a SELECT DISTINCT doctor from the temporary table. Sometimes I create a separate temporary table into which I would insert distinct data such as a doctor name and then declare the cursor on this table. Once the cursor is ready just step through the detail table using a command something like this: DELETE ROWS FROM temporary_table WHERE doctor = .vdoctor AND COUNT > 20 (or 30 or whatever). What you will end up with is a table that contains the most recent 20 or 30 procedures per doctor. I use a similar technique to do Pareto analysis of quality data and it works very well. If you need a more detailed explanation just let me know. Hope this helps. -- Mike Ramsour > -----Original Message----- > From: Bob Castanaro [SMTP:[EMAIL PROTECTED]] > Sent: Friday, December 14, 2001 5:15 PM > To: [EMAIL PROTECTED] > Subject: Limiting rows > > > > > > Hello all, > > I have a table that collects historical data for surgical procedures by > doctor, procedure and date (also collects times, and other info etc.) I > use this data for estimating stimes for scheduling purposes. What I need > to do is limit the info to the most recent 20 or 30 procedures for any > particular doctor and delete the rest. Some have less than 20 procedures, > some have hundreds. This table has about 25,000 rows, and I really don't > need data for how long it took Dr. X to take out a Gall Bladder 3 years > ago > when his most recent times are more relevant. > I've looked at a routine using cursors to scroll through and "group by" > but > I always seem to get stuck somewhere. > Any ideas? > Thanks in advance. > BC > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l
