• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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?
0
T-Virus
Asked:
T-Virus
  • 2
2 Solutions
 
LowfatspreadCommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
LowfatspreadCommented:
@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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now