Patti,
Maybe I still don't understand your problem well enough, but is seems to me you can solve this problem as follows: First of all I think your wanted lag function only works if there has been a contact of that kind earlier with that particular customer, but I am not familiar with the function In a formal way it is maybe better to create a table with customers and the types of contact that are needed for that particular customer. If they are the same for every customer you can fully automate this. If not then the user have to check / uncheck the needed types of contact per customer. Then you are able to insert rows in this table with the customer id and contact id, and a concatenated column of both columns (which should be unique btw) If you have done this, then the following solution is possible: 1. Create a view Y of all contacts less than 60 days 2. Then print a report based upon temp table X using the where clause: Where (customer_id + contact_id) not in (select (customer_id + contact_id) from View_Y) Most probably there are other and better solutions, but I think this one could work. Tony From: [email protected] [mailto:[email protected]] On Behalf Of Patti Jakusz Sent: zondag 3 maart 2013 16:32 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I could really use a LAG function (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 < <mailto:[email protected]> [email protected]> To: RBASE-L Mailing List < <mailto:[email protected]> [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 < <mailto:[email protected]> [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
