x
Solved

# Manipulate End Dates in a Migration Plan like Simulation

Posted on 2013-11-22
Medium Priority
150 Views
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.
0
Question by:T-Virus
• 2

LVL 50

Assisted Solution

ID: 39668619
you mean

update x
set [older 36 month date] = dateadd(mm,2,[older 36 month date])
from (select companyid , [older 36 month date]
, row_number() over (partition by companyid
order by [older 36 month date],[id]) as rn) as x
where companyid=?
and rn in (1,2)
or
Select x.*
, [new older 36 month date] = dateadd(mm,2,[older 36 month date])
from (select companyid , [older 36 month date]
, row_number() over (partition by companyid
order by [older 36 month date],[id]) as rn) as x
where companyid=?
and rn in (1,2)
order by companyid,id
0

LVL 34

Accepted Solution

Brian Crowe earned 1000 total points
ID: 39669203
Personally I prefer common table expressions but it boils down to the same logic...

WITH cte (ID, [Booking Date], [Older 36 Month Date], RowNumber
AS
(
SELECT ID, [Booking Date], [Older 36 Month Date],
ROW_NUMBER() OVER(ORDER BY [Older 36 Month Date] DESC) AS RowNumber
)
UPDATE cte
SET [Older 36 Month Date] = ???
WHERE RowNumber IN (1, 2)
0

LVL 50

Expert Comment

ID: 39669359
@bricrowe

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...
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.