Thanks Set!!!!!!!! I'll study it carefully and let you know... never thought could be done in a query, I was thinking in a store procedure to copy the table to a bidimensional array where I'd store clients and dates..and look for the dates differences in the array looking for the first > 30, I'm not used to think in terms of sets... I think I should get used to...
Thanks again

Svein Erling Tysvaer <svein.erling.tysvaer@...> escribió:
Your latest description of your problem seems to confirm that the
solution I tried to give, should work. However, I made (at least) one
little mistake and forgot one line of code:

AND M4.CLIENTID = M1.CLIENTID

Here's the corrected version (thinking that it feels more logical if M1
represents the lowest value, I also reversed what I consider M1, M2 etc):

//MIN select the lowest value amongst the returned records
//for the current CLIENTID, so the WHERE clause doesn't have
//to bother whether there is a lower match, EXCEPT for where
//there is no match > 30 days.

SELECT M1.CLIENTID, MIN(M1.DATE1) AS DATE1
FROM MYTABLE M1
JOIN MYTABLE M2 ON M2.CLIENTID = M1.CLIENTID
//Find records that are < NextRecord.date2-30
WHERE
M2.DATE2 > M1.DATE1+30
AND NOT EXISTS(
SELECT * FROM MYTABLE M3
WHERE M3.CLIENTID = M1.CLIENTID
AND M3.DATE1 BETWEEN M1.DATE1 AND M2.DATE1)
//Return the last date1 if not found
//First, check that it isn't found
OR (NOT EXISTS(
SELECT * FROM MYTABLE M4
JOIN MYTABLE M5 ON M5.CLIENTID = M4.CLIENTID
WHERE M5.DATE2 > M4.DATE1+30
AND M4.CLIENTID = M1.CLIENTID
AND NOT EXISTS(
SELECT * FROM MYTABLE M6
WHERE M6.CLIENTID = M1.CLIENTID
AND M6.DATE1 BETWEEN M4.DATE1 AND M5.DATE1))
//Then elimiate all prior date1
AND NOT EXISTS(
SELECT * FROM MYTABLE M7
WHERE M7.CLIENTID = M1.CLIENTID
AND M7.DATE1 > M1.DATE1))
GROUP BY 1

As for how you describe your problem, it would have been better if you
included actual dates rather than letters and showed some cases covering
every situation that you are interested in. As far as I understand, if
you have:

MYTABLE:
ClientID Date2 Date1
1 10.01.2007 01.01.2007
1 20.01.2007 13.01.2007
1 25.02.2007 01.02.2007
1 15.03.2007 27.02.2007
2 10.01.2007 01.01.2007
2 20.01.2007 13.01.2007
2 05.02.2007 01.02.2007

then you want the following result set:

ClientID Date1
1 13.01.2007 //25.02.2007-13.01.2007 is the first > 30 days
2 01.02.2007 //No consecutive records with > 30 days difference

and that is what I hope the above SQL will generate. You haven't
mentioned any records containing NULL in your problem description, so I
expect them to not be of any interest.

This particular query would benefit greatly from an index on ClientID.
Since you have no ClientID with more than 15 records (as you wrote in
borland.public.interbase.sql, you haven't said anything about it on this
list), other indexes will not be of any help to you.

If you feel the sql to be a bit complex, I think it can be simplified
(as far as it comes to human readability, I may or may not be simpler
for the optimizer) on Firebird 2.0 and probably InterBase 7.5 as well
(although I don't use either of them myself, I'm still on Firebird 1.5.2).

SELECT TempTable.ClientID, min(TempTable.Date1)
FROM (
SELECT M1.CLIENTID, M1.DATE1
FROM MYTABLE M1
JOIN MYTABLE M2 ON M2.CLIENTID = M1.CLIENTID
WHERE
M2.DATE2 > M1.DATE1+30
AND NOT EXISTS(
SELECT * FROM MYTABLE M3
WHERE M3.CLIENTID = M1.CLIENTID
AND M3.DATE1 BETWEEN M1.DATE1 AND M2.DATE1)
UNION
SELECT M4.ClientID, max(M4.Date1)
FROM MYTABLE M4
GROUP BY 1) as TempTable

HTH,
Set

lejandro Garcia wrote:

> Thanks for your time, I think that an example is the best way to explain i=
> t:
> The initial table is this, it's ordered by ClientId and Date2
> ClienID Date2 Date1
> 1 a b
> 1 c d
> 1 e f
> =20=20=20
> the first row is ClientID=3D 1, Date2 =3D a and Date1=3Db
> the next row is ClientID=3D1, Date2=3Dc and Date1=3Dd
> the next row is ClientID=3D1, Date2=3De and Date1=3Df
> the first difference is c-b, if this difference is >30 return (1,b) else =
> calculate the second difference: e-d, if this difference is >30 return (1,c=
> )
> if there's no difference >30 return the last Date1 for the ClientID
> and continue with the next ClientID to do the same..
> the result should be a 2 columns table (ClientID, Date1)
> =20=20=20
> The main problem I think is to process the actual and next row to calcula=
> te that differences
> =20=20=20=20
> =20=20=20
> =20=20
>
> Svein Erling Tysvaer <svein.erling.tysvaer@...> escribi=F3:
> Hi!
>
> First, I don't quite understand your problem, so this possible solution=20
> may be incorrect (nor have I tried to prepare any similar statement).=20
> Second, I'm not any good at writing stored procedures, so my suggestion=20
> is a single query, even though I think changing it to a stored procedure=20
> could be a good idea since it may look a bit complex at first sight. And=20
> I assumed row2.DATE1 > row1.DATE1.
>
> SELECT M1.CLIENTID, MIN(M2.DATE1) AS DATE1
> FROM MYTABLE M1
> JOIN MYTABLE M2 ON M2.CLIENTID =3D M1.CLIENTID
> //Find the lowest date1 which is < NextRecord.date2-30
> WHERE
> M1.DATE2 > M2.DATE1+30
> AND NOT EXISTS(
> SELECT * FROM MYTABLE M3
> WHERE M3.CLIENTID =3D M1.CLIENTID
> AND M3.DATE1 BETWEEN M2.DATE1 AND M1.DATE1)
> //Return the last date1 if not found
> //First, check that it isn't found
> OR (NOT EXISTS(
> SELECT * FROM MYTABLE M4
> JOIN MYTABLE M5 ON M4.CLIENTID =3D M5.CLIENTID
> WHERE M4.DATE2 > M5.DATE1+30
> AND NOT EXISTS(
> SELECT * FROM MYTABLE M6
> WHERE M6.CLIENTID =3D M1.CLIENTID
> AND M6.DATE1 BETWEEN M5.DATE1 AND M4.DATE1))
> //Then elimiate all prior date1
> AND NOT EXISTS(
> SELECT * FROM MYTABLE M7
> WHERE M7.CLIENTID =3D M1.CLIENTID
> AND M7.DATE1 > M2.DATE1))
> GROUP BY 1
>
> Up until the OR, it is how I think you want to calculate the difference=20
> between date1 and date2. You didn't specify what makes a row the 'next'=20
> row, so I guessed it was simply that there were no rows with DATE1=20
> between the two of them for this particular client.
>
> After the OR is simply to find the last date1 for the client in case=20
> there's no rows returned before the OR. To do that, I first check that=20
> there is no such rows and then that there is no later date.
>
> HTH,
> Set
>
> aleplgr wrote:
>> Hi! I hope this is the right forum, if not please let me know.
>> I'm working with EMS SQL MANAGER 2005 for INTERBASE, my InterBase=20
>> version is 7.5 and I need to create a sql procedure that recieves=20
>> one table as input and creates one table as output.
>> My input is a 3 columns table: ClientID, Date2 and Date1, for each=20
>> ClientID there may be several Date1 and Date2 values
>> =20
>> Now I have this:
>> ClientID Date2 Date1
>> 1 a b
>> 1 c d
>> 1 e f
>> =20
>> And I need to calculate the difference between the actual Date1 and=20
>> the next Date2 for each row until this difference is >30.
>> The difference for the first row is c-b, the difference for the=20
>> 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=20
>> days, for each ClientID.
>> If there's no Date1 which difference is greater than 30 days return=20
>> the last Date1 for this ClientID.
>> Thanks in advance

---------------------------------

LLama Gratis a cualquier PC del Mundo.
Llamadas a fijos y móviles desde 1 céntimo por minuto.
http://es.voice.yahoo.com

[Non-text portions of this message have been removed]