(I don't think I presented the problem well enough. There about 300 active clients in one table and each client has about 25 of a particular type of contact in another table. I have to make sure that there was <= 60 days between each of the 25 contacts.) But this may work. So I subtract Prev_Date from Max_Date and if > 60, add to the report. If <= 60, keep looking at the other contacts for that client by: Replacing Max_Date with Prev_Date and look for the next Prev_Date and keep doing it again for all the contacts for that client. Razzak and John: A Lag Function would be great for the next version of RBase!!!!!!!! Patti
From: Ben Petersen <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Saturday, March 2, 2013 10:11 PM Subject: [RBASE-L] - Re: I could really use a LAG function Select max(date_field) into Max_Date from Contacts_Table where Contact_ID = .x Select max(date_field) into Prev_Date from Contacts_Table + where Contact_ID = .x and Date_Field < .Max_Date Assuming there is a client table separate from contacts, it would drive the cursor. Even better, I would think this could be done with a view. Ben On Sat, Mar 2, 2013 at 6:41 PM, Patti Jakusz <[email protected]> wrote: Is there anything in Rbase similar to LAG, where you can compare a field in one row to a field in the previous row of a table? I'm always having to write programs where it would be so handy to just have a function like in SPSS and Oracle. > >My current task is one where I have a table of client contacts. I have to >count the number of days between certain types of contacts and include them on >a report if the number of days in between is over 60. > >All I can think to do is: >declare a cursor >read the 1st row, save the client contact date in a variable >read the 2nd row and subtract this contact date from the one stored in the >prev variable >save the result in a new field in the 1st row >continue the same procedure with all the rows >at the end of file, compare the last date to the date of the report. > >There's gotta be an easier way.Patti >
