SQL for intervals

Hi All,

I just start learning sql. I wonder whether you can help me with this: I have a table W for a warehouse in which each item has a unique id and a timestamp t.
Each record in the table is an id and the time stamp shows the day the item with that id was in the warehouse. One record can be
('123','1-1-1990') means item with id '123' is in the warehouse at date
'1-1-1990'
. In this table I want to find all the
intervals for which an item is present in the table. Assume item id 123 is in the warehouse with these timestampe:
('123','1-1-1990'),('123','1-2-1990'),('123','1-3-1990'),('123','1-4-1990'),
('123','1-10-1990'),('123','1-11-1990'). Notice there is a gap between
'1-4-1990 and '1-10-1990'
The outptut intervals I want for '123' are
('123','1-1-1990','1-4-1990') and
('123','1-10-1990','1-11-1990')

The first one means that '123' was in the warehouse from date '1-1-1990' to '1-4-1990' (continuously with no gap) and the second one means the item was also in the warehouse from '1-10-1990' to '1-11-1990' (continuously without gap)

I want to find all such intervals for each item.

Thank. Received on Fri Dec 01 2006 - 17:24:47 CST