CODEWITHSUNDEEP
t-sqlsql-server-2012
Capanga
Capanga

Reputation: 77

TSQL - Control a number sequence

Im a new in TSQL. I have a table with a field called ODOMETER of a vehicle. I have to get the quantity of km in a period of time from 1st of the month to the end. SELECT MAX(Odometer) - MIN(Odometer) as TotalKm FROM Table This will work in ideal test scenary, but the Odomometer can be reset to 0 in anytime. Someone can help to solve my problem, thank you.

I'm working with MS SQL 2012

EXAMPLE of records:

 Date          Odometer value
 datetime var, 37210
 datetime var, 37340
 datetime var, 0
 datetime var, 220

Upvotes: 1

Views: 92

Answers (2)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Try something like this using the LAG. There are other ways, but this should be easy.

EDIT: Changing the sample data to include records outside of the desired month range. Also simplifying that Reading for easy hand calc. Will shows a second option as siggested by OP.

DECLARE @tbl TABLE (stamp DATETIME, Reading INT)
INSERT INTO @tbl VALUES 
  ('02/28/2014',0)
 ,('03/01/2014',10)
 ,('03/10/2014',20)
 ,('03/22/2014',0)
 ,('03/30/2014',10)
 ,('03/31/2014',20)
 ,('04/01/2014',30)

--Original solution with WHERE on the "outer" SELECT.
--This give a result of 40 as it include the change of 10 between 2/28 and 3/31. 

;WITH cte AS (
   SELECT Reading
         ,LAG(Reading,1,Reading) OVER (ORDER BY stamp ASC) LastReading
         ,Reading - LAG(Reading,1,Reading) OVER (ORDER BY stamp ASC) ChangeSinceLastReading 
         ,CONVERT(date, stamp) stamp
     FROM @tbl
)
SELECT SUM(CASE WHEN Reading = 0 THEN 0 ELSE ChangeSinceLastReading END)
  FROM cte
 WHERE stamp BETWEEN '03/01/2014' AND '03/31/2014'

--Second option with WHERE on the "inner" SELECT (within the  CTE)
--This give a result of 30 as it include the change of 10 between 2/28 and 3/31 is by the filtered lag.

;WITH cte AS (
   SELECT Reading
         ,LAG(Reading,1,Reading) OVER (ORDER BY stamp ASC) LastReading
         ,Reading - LAG(Reading,1,Reading) OVER (ORDER BY stamp ASC) ChangeSinceLastReading 
         ,CONVERT(date, stamp) stamp
     FROM @tbl
    WHERE stamp BETWEEN '03/01/2014' AND '03/31/2014'
)
SELECT SUM(CASE WHEN Reading = 0 THEN 0 ELSE ChangeSinceLastReading END)
  FROM cte

Upvotes: 2

thepirat000
thepirat000

Reputation: 13114

I think Karl solution using LAG is better than mine, but anyway:

;WITH [Rows] AS
(
    SELECT o1.[Date], o1.[Value] as CurrentValue, 
                (SELECT TOP 1 o2.[Value] 
                FROM @tbl o2 WHERE o1.[Date] < o2.[Date]) as NextValue
    FROM @tbl o1
)
SELECT SUM (CASE WHEN [NextValue] IS NULL OR [NextValue] < [CurrentValue] THEN 0 ELSE [NextValue] - [CurrentValue] END )
FROM [Rows]

Upvotes: 0

Related Questions