I need to explain myself better. I need to write a report that shows a therapist's caseload for each month of the past two years.
The problem is that I do not have a closed date for the old therapist when an client is transferred to a new therapist. The table contains the fields caseno, clientname, server, opendate. In order to recreate the caseload for a therapist in the past, I have to know the period of time that a client was assigned to the therapist. I can make an assumption that when the client was assigned to a new therapist it ended the assigment of the old therapist. I need to add a column (closed date) and somehow set the closed date column to equal the new assignment date for the new therapist. ---- Original Message ---- From: [EMAIL PROTECTED] To: [email protected] (RBG7-L Mailing List) Subject: RE: [RBG7-L] - Re: updating a field Date: Wed, 20 Jul 2005 11:50:26 -0700 (PDT) >I seldom use start and stop dates. I use only the start dates, and >use the power of SELECT to give >me the stops. I have worked with payroll a fair bit, and the >effective date is > >(MAX(EffDate)) FROM ... WHERE EffDate <= WorkDate > >In the case you are looking for, I would use something along the >lines of > >SELECT t1.CaseNo, MAX(t1.ClientName), MAX(t1.Server), t1.OpenDate, + > (MIN(t2.OpenDate)) AS ClsoeDate + > FROM CaseTable t1, CaseTable t2 + > WHERE t1.CaseNo = t2.CaseNo AND t2.OpenDate > t1.OpenDate + > GROUP BY t1.CaseNo, t1.OpenDate > >The two MAX() are just to eliminate the comparisons for the GROUP BY > >I hope this helps! > >--- gries <[EMAIL PROTECTED]> wrote: > >> Hello everyone, >> >> I have a table that has the following columns; caseno, clientname, >> server, opendate. >> >> The data consists of all clients and their assignments to a >therapist >> along with the date of the assignment. If a client was ever >> reassigned to a new therapist then there would be multiple records >> for that client. >> >> What I need to do is to add a column (closedate) to the table and >set >> the column to equal the open date of the previous record if there >are >> multiple records for that client, thus giving me a close date for >> that record. >> >> I need to leave those without multiple records blank. >> >> The table is sorted by clientname, opendate asc. >> >> Thanks for your help. I miss you all! >> >> Ron >> >> > > >Albert Berry >Management Consultant >RR2 - 1252 Ponderosa Drive >Sparwood BC, V0B 2G2 >Canada >(250) 425-5806 >(250) 425-7259 >(708) 575-3952 (fax) >[EMAIL PROTECTED] >
