Convert from Oracle to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert from Oracle to SQL Server

Author

Topic

smithtod
Starting Member

5 Posts

Posted - 2009-10-23 : 14:58:43

I need help converting this SQL from Oracle to SQL Server

SELECT a.*, b.*, (a.appstart - b.appfinish)

FROM (SELECT ROWNUM rid, appstart, appfinish

FROM schedule

ORDER BY 2, 3) a,

(SELECT ROWNUM rid, appstart, appfinish

FROM schedule

ORDER BY 2, 3) b

WHERE a.rid = (b.rid + 1)

AND a.appfinish <> b.appfinish

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-23 : 15:23:48

I think this should be an equivalent: (assuming it's sql server 2005 or later)


;with s (rid, appstart, appfinish)
as
(
       select row_number() over (order by appstart, appfinish)
              ,appstart
              ,appfinish
)
select a.*, b.*, (a.appstart - b.appfinish) 
from   s a
inner join s b
       on b.rid + 1 = a.rid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources