CODEWITHSUNDEEP
sqlt-sql
Scott Stafford
Scott Stafford

Reputation: 44818

Calculating de-cumulatived values in TSQL?

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

Upvotes: 1

Views: 145

Answers (4)

Richard Deeming
Richard Deeming

Reputation: 31248

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.

Upvotes: 2

Nico
Nico

Reputation: 1197

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271061

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

Upvotes: 1

Chains
Chains

Reputation: 13167

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)

Upvotes: 1

Related Questions