Need a view to return a field in the last record b

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

Need a view to return a field in the last record b

Author

Topic

Goethe
Starting Member

4 Posts

Posted - 2012-03-12 : 16:15:06

Good evening.

This is my first post in here, thanks for all the help in advance, I´m doing a program for a transporting company, and I require some help to create a view for my FUEL SUPPLY TABLE.

I have a table called FUEL, that has several fields, but I´m gonna list only the ones I need help.

TABLE FUEL
Fields:
- ID
- DATE
- VEHICLE_PLATE
- CURRENT_MILEAGE

I need to create a view that shows this data, and creates A FIFTH field showing to me the PREVIOUS MILEAGE, meaning, finding in the previous record with the same VEHICLE_PLATE than the one listed, the CURRENT_MILEAGE, and display it in the current, ordered by date.

I hope I was able to explain my problem properly,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 16:31:28

[code]
CREATE VIEW YourViewName
AS
SELECT t. ID,
t.DATE,
t.VEHICLE_PLATE,
t.CURRENT_MILEAGE,
t1.CURRENT_MILEAGE AS PREVIOUS_MILEAGE
FROM table t
CROSS APPLY (SELECT TOP 1 CURRENT_MILEAGE
FROM table
WHERE DATE < t.DATE
ORDER BY DATE DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Goethe
Starting Member

4 Posts

Posted - 2012-03-12 : 17:51:02

Thanks for the quick reply, but there is a problem with this view, it gets the field CURRENT_MILEAGE of the previous record, however, I need to get that field from the previous record that has the same VEHICLE_PLATE

Goethe
Starting Member

4 Posts

Posted - 2012-03-12 : 17:56:49

I was able to solve it, by adding the VEHICLE_PLATE inside the CROSS APPLY.

Thank you for all the help, I have very little knowledge of of SQL Programming, I wasn't going to be able to solve this without your help visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 18:20:31

quote:
Originally posted by Goethe

I was able to solve it, by adding the VEHICLE_PLATE inside the CROSS APPLY.

Thank you for all the help, I have very little knowledge of of SQL Programming, I wasn't going to be able to solve this without your help visakh16.

welcome

See what all you can do with APPLY operator

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources