Hello,

I use Sybase IQ/15.1.0.5034.

Let's say I have this data:

Code:
Table products =
product    date
1          2000-01-01
1          2000-01-02
1          2000-01-06
2          2000-01-03
2          2000-01-05
2          2000-01-10
I want to create a table with two columns containing, for each row, on a product basis, the number of days since the previous row ; and from the first row (in time):


Code:
product     date         prev      first
1           2000-01-01   0         0
1           2000-01-02   1         1
1           2000-01-06   4         5
2           2000-01-03   0         0
2           2000-01-05   2         2
2           2000-01-10   5         7
One solution is to use subselects, something like:

Code:
select t.product,
  (select min(t.date - s.date) from products as s where s.product = t.product and s.date < t.date) as prev,
  t.date - (select min(s.date) from products as s where s.product = t.product) as first
from products as t
However, I have concerns about the efficiency of this solution. I'm considering cursors, but I have problems with the Sybase syntax (see http://www.dbforums.com/sybase/16650...r-but-why.html) and also popular culture deems cursors "bad" and encourage using set-oriented only solutions.

What's your opinion or advice ?

Thanks,
J-L