Next Row for TimeTable - SQL Server Forums

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

Next Row for TimeTable

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-05-04 : 11:34:09

Hi all,

I know this topic repeatedly comes up. I'm working on setting up a timetable for bus routes and the query that I have produces the expected results, but I was wondering if there may be a more efficient way to do this. The query and table description I'm about to post is a tiny sample of the data; when I use this query against a large data set, it is very slow. For example, running 1 day's worth of data will take about 2 minutes; and I generally need to run it for 2-3 months at a time.

Any advice would be appreciated!

[CODE]
declare @RouteTable table ( RTid int primary key identity not null
,Sdate date not null
,BlockNum int not null
,RouteNum int not null
,WorkFromTime time not null
,WorkToTime time not null
,TripFromTime time not null
,TripToTime time not null
)

insert into @RouteTable (Sdate, BlockNum, RouteNum, WorkFromTime, WorkToTime, TripFromTime, TripToTime)
values

('2012-01-01', 1,101,'07:00:00', '13:00:00', '07:45:00', '08:10:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '08:15:00', '08:40:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '08:45:00', '09:10:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '09:15:00', '09:40:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '09:45:00', '10:10:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '10:15:00', '10:40:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '10:45:00', '11:10:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '11:15:00', '11:40:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '11:45:00', '12:10:00'),
('2012-01-01', 1,101,'07:00:00', '13:00:00', '12:15:00', '12:40:00')

;with R (Sdate, BlockNum, RouteNum, WorkFromTime, WorkToTime, TripFromTime, TripToTime, RowNum)
as
(
select Sdate, BlockNum, RouteNum, WorkFromTime, WorkToTime, TripFromTime, TripToTime,
ROW_NUMBER() OVER (Partition by Sdate, BlockNum Order by TripFromTime) as RowNum
from @RouteTable
)

--//Use Trip B FromTime as Trip A's ToTime except for last trip
select R.Sdate, R.BlockNum, R.RouteNum, R.WorkFromTime, R.WorkToTime, R.TripFromTime, COALESCE(R2.TripFromTime, R.TripToTime) as TripToTime
from R
left join
(
select Sdate, BlockNum, RouteNum, WorkFromTime, WorkToTime, TripFromTime, TripToTime, RowNum
from R
) R2 on R2.Sdate=R.Sdate and R2.BlockNum=R.BlockNum and R2.RowNum=R.RowNum+1
[/CODE]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2012-05-04 : 11:53:09

Not really...
That's why SQL Server 2012 brings great improvements in this area like the LEAD and LAG functions.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 2.0 out!

SQL Server MVP

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-05-04 : 11:58:54

Bummer! Ok, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources