Calculating de-cumulatived values in TSQL?

Question

Given a table of cars and their odometer reading at various dates (first of each month), how can I write TSQL (ideally, for use as a SQL Server view) to return the "incremental" values?

In other words, I want the reverse operation from Calculate a Running Total in SQL Server.

Example:

On this table:

CarId |   Date    | Mileage
---------------------------
    1    1/1/2000    10000
    1    2/1/2000    11000
    1    3/1/2000    12000
    2    1/1/2000    10000
    2    2/1/2000    11001
    2    3/1/2000    12001
    3    1/1/2000    10000
   (missing datapoint for (3, 2/1/2000))
    3    3/1/2000    12000

We'd return something like (the details/edge cases are flexible):

CarId |   Date    | Delta
---------------------------
    1    1/1/2000    10000
    1    2/1/2000    1000
    1    3/1/2000    1000
    2    1/1/2000    10000
    2    2/1/2000    1001
    2    3/1/2000    1000
    3    1/1/2000    10000
    3    3/1/2000    2000

Richard Deeming · Answer 1 · 2013-02-13T12:04:31.810

2

This should work for SQL 2005 or higher:

WITH cteData As
(
   SELECT
      CarId,
      Date,
      Mileage,
      ROW_NUMBER() OVER (PARTITION BY CarId ORDER BY Date) As RowNumber
   FROM
      dbo.Cars
)
SELECT
   C.CarId,
   C.Date,
   CASE
      WHEN P.CarId Is Null THEN C.Mileage
      ELSE C.Mileage - P.Mileage
   END As Delta
FROM
   cteData As C
   LEFT JOIN cteData As P
   ON P.CarId = C.CarId
   And P.RowNumber = C.RowNumber - 1
ORDER BY
   C.CarId,
   C.Date
;

SQL Fiddle

NB: This assumes that "missing datapoint for (3, 2/1/2000)" means that there is no row in the table for car 3, February 2000.

edited Feb 13 '13 at 12:04

answered Feb 12 '13 at 19:52

Richard Deeming

29,830
10
79
151

@Nico: Really? I've just tried it, and it looks OK to me: http://www.sqlfiddle.com/#!3/d41d8/8780 – Richard Deeming Feb 13 '13 at 12:00
It depends on base data. I ran your query against data provided by tamago via insert query ( see below ). If NULL value exists, query does not return proper results ( or maybe not the estimated ones ;) ) - if there's no row at all in base data, query runs fine! – Nico Feb 13 '13 at 12:13

score 1 · Answer 2 · edited Feb 12 '13 at 21:23

1

Window functions are great. But SQL Server does not have the one you need until SQL Server 2012. There, you have the lag function:

select t.*,
       (Milage - lag(Milage) over (partition by carId order by date)) as Delta
from t

For earlier versions, you can use a correlated subquery:

[trouble uploading query], alas.

    select t.*, (Mileage - prevMileage) as Delta
    from (select t.*,     
                 (select top 1 Mileage    from t t2
                  where t2.carId = t.carId and t2.date < t.date order by desc
                ) as prevDelta 
      from t
     ) t

edited Feb 12 '13 at 21:23

Beth

9,531
1
24
43

answered Feb 12 '13 at 20:04

Gordon Linoff

1,242,037
58
646
786

I apologize. Every time I try to better format that query, StackOverflow gives me an error. – Gordon Linoff Feb 12 '13 at 20:09

score 1 · Answer 3 · answered Feb 12 '13 at 20:54

Trying to do this without dependence on any 2012 functions, cursor, while loop, etc.

This works within some limitation -- namely, the null-entry for car#3's entry is a problem for it:

DECLARE @cars table ([id] int, [date] smalldatetime, [mileage] int)
INSERT INTO @cars ([id], [date], [mileage])
SELECT 1, '1/1/2000', 10000 UNION ALL
SELECT 1, '2/1/2000', 11000 UNION ALL
SELECT 1, '3/1/2000', 12000 UNION ALL
SELECT 2, '1/1/2000', 10000 UNION ALL
SELECT 2, '2/1/2000', 11000 UNION ALL
SELECT 2, '3/1/2000', 12000 UNION ALL
SELECT 3, '1/1/2000', 10000 UNION ALL
SELECT 3, '2/1/2000', NULL UNION ALL
SELECT 3, '3/1/2000', 12000


SELECT t1.id, t1.date, t1.mileage, t2.id, t2.date, t2.mileage, t1.mileage - t2.mileage as miles FROM @cars t1
LEFT JOIN @cars t2
ON t1.id = t2.id
AND t1.date = DATEADD(MONTH,1, t2.date)

score 1 · Accepted Answer · answered Feb 12 '13 at 22:48

1

Same approach as the one from @Richard Deeming, but this one regards possible null values as included in original question.

;with cte ( rn, id, date, mileage )
as
(
  select
     row_number() over ( partition by id order by id, date )
     , id
     , date
     , mileage
   from
      cars
   where
      mileage is not null
)
select
  "current".id
  , "current".date
  , delta = isnull( "current".mileage - predecessor.mileage, "current".mileage )
from
  cte as "current"
  left join cte as predecessor
    on "current".id = predecessor.id
    and "current".rn - 1 = predecessor.rn

See SQL-Fiddle.

answered Feb 12 '13 at 22:48

Nico

1,175
15
33

I was assuming that "missing datapoint" meant that there wasn't a row in the table, not that there was a row but the mileage was `NULL`. – Richard Deeming Feb 13 '13 at 12:01
maybe a point-of-view-thing. As commented in your answer, I refered to base-data-insert seen in one of the other answers. – Nico Feb 13 '13 at 12:14
In my case particularly, we do have records with nulls that should be ignored. – Scott Stafford Feb 25 '13 at 15:52

Calculating de-cumulatived values in TSQL?

4 Answers4