By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,525 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,525 IT Pros & Developers. It's quick & easy.

SQL Servers equivilient for Oracles Lead and Lag

P: 11
Hello everyone, i have some data:



i am trying to create a 4th column called 'Nightly Mileage' which would essentially be DailyMileage(row1)-MileageAtStartOfShift(Row2)

not sure how to do an aggregate function over data from a different row, i tried this:

Expand|Select|Wrap|Line Numbers
  1. SELECT MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, 
  2. MileageAtEndOfShift - MileageAtStartOfShift.lead(1) ON 0 AS NightMileage
  3. FROM         dbo.DailyMileages
but this just returns error
Incorrect syntax near the keyword 'ON'.
how can i do this? any ideas?

Thanks in advance, Regards

J.
Mar 12 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
What's the primary key on the table?

-- CK
Mar 13 '08 #2

P: 11
What's the primary key on the table?

-- CK
The Primary Hey is a field called DailyFuel_ID and it is an Identity Field Also.

one other thing that might help you answer my question is i am only using sql server 2000 (i'm guessing thats why the MDX function lead/lag is not working).

i was thinking of doing this with some kind of subselect with ID+1 or something, will have a play when i get in work.

but we are going to be dealing with large tables here, so just though i'd see if there is a better way of doing it..

Thanks again in advance,

Regards

J.
Mar 13 '08 #3

P: 11
ive come up with a solution via implementing a simple subselect, works, but not sure what the performance implications are going to be.

Query if anyone is interested:
Expand|Select|Wrap|Line Numbers
  1. select * from SELECT     DailyMileageID, MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, MileageAtEndOfShift -
  2.                           (SELECT     MileageAtStartOfShift
  3.                             FROM          dbo.DailyMileages AS I
  4.                             WHERE      (DailyMileageID = b.DailyMileageID + 1)) AS NightMileage, ShopID, DateEntered
  5. FROM         dbo.DailyMileages AS b
Mar 13 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.