Next rows data on previous row

[BakshG]

Here is my situation:

CREATE TABLE Test
(Rowid int,
Dt Datetime)

INSERT INTO Test VALUES(1, '01/01/2004')
INSERT INTO Test VALUES(2, '01/15/2004')
INSERT INTO Test VALUES(3, '01/31/2004')

and I want data like:

Rowid start_dt end_dt
1 01/01/2004 01/14/2004
2 01/15/2004 01/30/2004
3 01/31/2004 Today's Date

So in a nut shell I want next rows date minus 1 day on the previous row and if there is no next row then get today's date.

Please note that the RowId column is not sequential and neither the date is equally apart.

Thanks for all your help

 

[nagornyi]

select
  t1.rowid
, t1.dt start_dt
, nvl(t2.dt, sysdate) end_dt
from
  (select rowid, dt, rownum num from test) t1
, (select rowid, dt-1 dt, rownum num from test) t2
where t1.num = t2.num(+)+1

 

[BakshG]

Thanks very much but this solution will not work in SQL as there is no rownum.

 

[dnoeth]

Which RDBMS? Looks like MS SQL Server...

Newer versions of Oracle/DB2/Teradata support OLAP window functions:

select
rowid,
coalesce(dt - MIN(dt) OVER (ORDER BY rowid
ROWS BETWEEN 1 FOLLWING AND 1 FOLLWING), CURRENT_DATE)
from test

Is dt ascending according to rowid, i.e. a higher rowid means a higher date value?

select
rowid,
coalesce(select min(dt) from test t2
where t2.rowid > t1.rowid), current_date)
from test t1;

This work on every DBMS with scalar subqueries, but the first query is more efficient.

Dieter

 

[BakshG]

Thanks Dieter!!
Yes it is MS SQL server (sorry for not mentioning earlier). Your second solution works just fine.

I took the very first solution and tried to make it work on MS SQL server as follows: (can you suggest if this is a good way of doing also)


SELECT
one.rowid,
one.dt AS start_date,
ISNULL(two.dt, GETDATE()) AS end_date
FROM
(
SELECT
COUNT(*) rownum, a.rowid, a.dt
FROM
test a
JOIN test b
ON
a.rowid <= b.rowid
GROUP BY
a.rowid, a.dt
) one
LEFT JOIN
(
SELECT
COUNT(*) rownum, a.rowid, DATEADD(DAY, -1, a.dt) AS dt
FROM
test a
JOIN test b
ON
a.rowid <= b.rowid
GROUP BY
a.rowid, a.dt
) two
ON
one.rownum = two.rownum + 1

 

[dnoeth]

Just look at the optimizer plan and you'll see that my version is not that efficient, but this one is even worse. Only with Oracle's built in ROWNUM this might be not that bad ;-)

Dieter

 

[BakshG]

Thanks guys for all your help. Specially Dieter!!