🚀 go-pugleaf

aleplgr wrote:
> WillR <willrNo.Spam.pmccl.noSpam.com> wrote:
>> aleplgr wrote:
>>> "Patrick Moloney" <NewsID*at*SandrockSoftware.c*o*m> wrote:
>>>> aleplgr wrote:
>>>>
>>>>> "Patrick Moloney" <NewsID*at*SandrockSoftware.c*o*m> wrote:
>>>>>> Why do you want to "return a table"?  This sounds like you don't
>>>>>> want separate client tables in Interbase? How many clients are
>>>>>> there? Is that a lot of tables?
>>>>>>
>>>>>> -- 
>>>>>> Patrick Moloney
>>>> It's hard understanding what you need to do.  Each message you post
>>>> gives a little more information about what you want. You also include
>>>> bits of your proposed solution - tables etc.  You would probably get a
>>>> better response with less guessing by everyone if you put the effort to
>>>> make a clear and complete statement of what you need to do, and leave
>>>> out your suggestions for the solution in this step one.  It is often
>>>> said that asking the right question is more than half the answer.
>>>>
>>>> -- 
>>>> Patrick Moloney
>>>
>>> OK, I didn't mean to be confusing.
>>> I have a 3 columns table: ClientID, Date1 and Date2.
>>> There are at most 1000 different ClientIDs and 10000 rows in this table, it's ordered by ClientID and Date1.
>>> For each ClientID there's at least one pair (Date1,Date2) and at most 10 pairs (Date1,Date2).
>>> I need to return one table with 2 columns: ClientID and Date1 where
>>> if the difference between the actual Date1 and next(Date2) >30 return actual Date1 else if there is no such difference >30 return the greatest Date1 for the actual ClientID. 
>>> next(Date2) is the Date2 that is in the next row of the actual ClientID.
>>> The DBMS is InterBase 7.5 
>> But you also mentioned feeding SPSS -- Couldn't you use the Data Direct 
>> ODBC driver to query the data directly from Interbase?
>>
>> It does work -- I am feeding MapInfo from Interbase 7 -- directly via 
>> the ODBC driver.
>>
>> So it seems your description is still not complete. If there is more 
>> information to feed -- especially with calculations maybe a direct feed 
>> and some views are the best answer.
>>
>> If there is a lot of calculation maybe a client program is best... I 
>> looked at using Stored procedure to process our portable "magnetometer" 
>> data but then decided to use a Delphi routine since it became clear the 
>> logic was pretty tortuous and would become more so as we looked at more 
>> conditions to "flag" data. Then I present the data with the flags set 
>> and the geologist can map the conductors detected  as they see fit. The 
>> flags define sets of data with specific conditions entitling them to 
>> pretty little symbols on the maps -- for example.
>>
>> -- 
>> Will R
>> PMC Consulting
> 
> 
> I don't need to  automatically feed SPSS, it's enough to create a table, here is a better explanation of what I need: 
>  sql procedure to process one table and generate another table as output,  Now I have a 3 columns table: ClientID, Date2 and Date1, for each ClientID there may be several Date1 and Date2 values
>  
> Now I have this:
> ClientID  Date2     Date1
> 1           a          b
> 1           c          d
> 1           e          f
> 
> And I need to calculate the difference between the actual Date1 and the next Date2 for each row until this difference is >30.
> The difference for the first row is c-b, the difference for the second row is e-d.
> I need to return a table with 2 columns: ClientID and Date1.
> This Date1 is the first such that the difference is greater than 30 days, for each ClientID.
> If there's no Date1 which difference is greater than 30 days return the last Date1 for this ClientID.
> Thanks in advance
> 

Why don't you present the data in reverse order and start with your 
answer being (f) -- the rest should be obvious...

read a line
answer = f
store date 2 (e)in olddate2

while not done
begin
   readaline
   process the data  -- Olddate2 minus newdate1  // this is (e-d)1st time
   if diff > 30 answer = current line date1 else do nothing

end




-- 
Will R
PMC Consulting