Hey Ron, are you a Doctor? Your weekends start on Thursday? Paula Stuart Community Development Strategies 70 Summer Street Somerville, MA 02143 617-776-3367 617-776-4554 (Fax) 617-388-1331 (Cell) [EMAIL PROTECTED]
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Ron Gries Sent: Wednesday, July 20, 2005 7:11 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: updating a field Thanks Albert, I'm going to try that Tuesday as I will be out of town for the weekend. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Albert Berry Sent: Wednesday, July 20, 2005 5:01 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: updating a field Perhaps I did not explain my SELECT statement correctly - and reviewing it, I notice that I did not cover the case where the client is still current with a therapist. If you modify the SELECT statement to read like this, and create it as a view, you will have what you are looking for, I think. Base your report on the view. The double table call is used to find the closing date without storing the redundant data. CREATE VIEW vwCaseLoad (CaseNo, ClientName, Server, OpenDate, CloseDate AS + SELECT t1.CaseNo, MAX(t1.ClientName), MAX(t1.Server), t1.OpenDate, + (MIN(IFNULL(t2.OpenDate,.#DATE,t2.OpenDate))) + FROM CaseTable t1, CaseTable t2 + WHERE t1.CaseNo = t2.CaseNo AND t2.OpenDate > t1.OpenDate + GROUP BY t1.CaseNo, t1.OpenDate --- gries <[EMAIL PROTECTED]> wrote: > 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 > > > > > >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] > > > > 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]
