Manipulate End Dates in a Migration Plan like Simulation
Hello together,
let me borrow your brains for a minute - i would like to do the following Simulation in Sql.
Assume you have leased 5 cars to a company.
Now every month you would like to replace the oldest 2 cars with new ones.
Sample Current Data:
[ID] [Booking Date] [Older 36 Month Date]
1 2010-01-01 2013-01-01
2 2010-01-01 2013-01-01
3 2010-01-01 2013-01-01
4 2010-01-01 2013-01-01
5 2010-02-01 2013-02-01
Sample Output new Data:
[ID] [Booking Date] [Older 36 Month Date]
1 2010-01-01 2013-01-01
2 2010-01-01 2013-01-01
3 2010-01-01 2013-02-01
4 2010-01-01 2013-02-01
5 2010-02-01 2013-03-01
As you can see i would like to manipulate the [Older 36 Month Date] for 2 cars at a time.
How would i go about this in SQL?
What would be your solution?
let me borrow your brains for a minute - i would like to do the following Simulation in Sql.
Assume you have leased 5 cars to a company.
Now every month you would like to replace the oldest 2 cars with new ones.
Sample Current Data:
[ID] [Booking Date] [Older 36 Month Date]
1 2010-01-01 2013-01-01
2 2010-01-01 2013-01-01
3 2010-01-01 2013-01-01
4 2010-01-01 2013-01-01
5 2010-02-01 2013-02-01
Sample Output new Data:
[ID] [Booking Date] [Older 36 Month Date]
1 2010-01-01 2013-01-01
2 2010-01-01 2013-01-01
3 2010-01-01 2013-02-01
4 2010-01-01 2013-02-01
5 2010-02-01 2013-03-01
As you can see i would like to manipulate the [Older 36 Month Date] for 2 cars at a time.
How would i go about this in SQL?
What would be your solution?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ROW_NUMBER() OVER(ORDER BY [Older 36 Month Date] DESC)
shouldn't that be asc?
he wants to update the "oldest" , rather than "latest"?
but yes i agree simple CTE can read better...