Thanks for your time, I think that an example is the best way to explain it:
The initial table is this, it's ordered by ClientId and Date2
ClienID Date2 Date1
1 a b
1 c d
1 e f

the first row is ClientID= 1, Date2 = a and Date1=b
the next row is ClientID=1, Date2=c and Date1=d
the next row is ClientID=1, Date2=e and Date1=f
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)

The main problem I think is to process the actual and next row to calculate that differences




Svein Erling Tysvaer <svein.erling.tysvaer@...> escribió:
Hi!

First, I don't quite understand your problem, so this possible solution
may be incorrect (nor have I tried to prepare any similar statement).
Second, I'm not any good at writing stored procedures, so my suggestion
is a single query, even though I think changing it to a stored procedure
could be a good idea since it may look a bit complex at first sight. And
I assumed row2.DATE1 > row1.DATE1.

SELECT M1.CLIENTID, MIN(M2.DATE1) AS DATE1
FROM MYTABLE M1
JOIN MYTABLE M2 ON M2.CLIENTID = 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 = 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 = M5.CLIENTID
WHERE M4.DATE2 > M5.DATE1+30
AND NOT EXISTS(
SELECT * FROM MYTABLE M6
WHERE M6.CLIENTID = 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 = 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
between date1 and date2. You didn't specify what makes a row the 'next'
row, so I guessed it was simply that there were no rows with DATE1
between the two of them for this particular client.

After the OR is simply to find the last date1 for the client in case
there's no rows returned before the OR. To do that, I first check that
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
> version is 7.5 and I need to create a sql procedure that recieves
> one table as input and creates one table as output.
> My input is 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

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

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]