Add rows in a table after check it with another table

Question

I've this table "Mileage" with the following data

user   DateMil        Dist
 10   2014-09-04      102
 10   2014-09-07      250
 15   2014-09-04      180
 15   2014-09-06      135

and another table "Initialization" with the following data

user   DateInit         Init
 10   2014-09-03      12500
 15   2014-09-03      20000

How i need is get the last of data of table "Initialization" and join it with table of "Mileage" with the same user and add a new rows in the table of "Initialization".The resultant table should be as below

user   DateInit         Init
 10   2014-09-03      12500
 10   2014-09-04      12602(12500+102=last(init)+Dist)
 10   2014-09-07      12852(12602+250)
 15   2014-09-03      20000
 15   2014-09-04      20180(2000+180)
 15   2014-09-06      20315(20180+135)

Answer 1

You can use a CTE to put together a UNION of the two original tables. Then, if you are using SQL SERVER 2012+, you can use SUM to calculate running totals:

 ;WITH CTE AS (
    SELECT [user], DateMil AS DateInit, Dist AS Init
    FROM Mileage

    UNION

    SELECT [user], DateInit, Init 
    FROM Initialization
)
SELECT [user],  DateInit, 
       SUM(Init) OVER (PARTITION BY [user] ORDER BY DateInit) As Init
FROM CTE

SQL Fiddle Demo here