CODEWITHSUNDEEP
sqlsql-serversql-server-2012subtraction
Dodgeball
Dodgeball

Reputation: 125

Minus the lower row from top row all the way down in sql

I have a query which is stated below which show the total km run of my vehicle. Now I want to make a subtraction. I want this like if I have a sample below:

date          totkm
2015-27-01    81199
2015-27-01    81198
2015-27-01    81197

Now, what I want as a result is something like this:

date         totkm
2015-27-01    0
2015-27-01    1
2015-27-01    1

As you can see I subtracted the first row and second row then subtracted the second row and the third row and so on and so forth.

Really would like to ask for some help.

Here's my query:

SELECT 
    ROW_NUMBER() OVER(ORDER BY CONVERT(char(10), TRXDATE, 126) DESC) AS Row,
    (CONVERT(char(10), TRXDATE, 126)) AS NEWDATE, 
    kmrun AS totkm 
FROM [SMSGPS].[dbo].tblCUrrent_ROI 
WHERE 
    TRXDATE BETWEEN '2015-01-26 06:00:00' AND '2015-01-26 08:30:00'
    AND PLATENO = 'NILO-MOTORCYCLE' 
ORDER BY CONVERT(char(10), TRXDATE, 126) DESC

Upvotes: 0

Views: 355

Answers (2)

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

Since you have default order of records in your table, I am not applying ORDER BY on Date column. Instead I use SELECT 0 to get the order for ROW_NUMBER.

I am writing query in SQL Server 2008, that supports in SQL Server 2012 too.

SAMPLE TABLE

CREATE TABLE #tblCUrrent_ROI([DATE] DATE,TOTKM VARCHAR(50)) 

INSERT INTO #tblCUrrent_ROI
SELECT '2015-01-27',81199
UNION ALL
SELECT '2015-01-27',81198
UNION ALL
SELECT '2015-01-27',81197

QUERY

;WITH CTE AS
(
    -- Row number in default order
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0))RNO,*
    FROM #tblCUrrent_ROI
)
SELECT C1.RNO,C1.[DATE],ISNULL(CAST(C2.TOTKM AS INT)-CAST(C1.TOTKM AS INT),0) KM
FROM CTE C1
LEFT JOIN CTE C2 ON C1.RNO=C2.RNO+1

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use LAG function.

CREATE TABLE #temp(
    [Date]  DATE,
    TotKM   VARCHAR(50)
)
INSERT INTO #temp VALUES
('20150127', '81199'),
('20150127', '81198'),
('20150127', '81197');

SELECT
    [Date],
    TotKM = LAG(CAST(TotKM AS INT), 1, CAST(TotKM AS INT)) OVER(ORDER BY [Date], CAST(TotKM AS INT) DESC) - TotKM
FROM #temp

DROP TABLE #temp

Upvotes: 2

Related Questions