Minus Column for current month from previous month

Question

Post reply

Minus Column for current month from previous month

ringovski

Hall of Fame

Points: 3521
More actions
November 24, 2016 at 6:20 pm

#319327

Hi,
Hopefully someone can help.
I need the difference between LastOdo (Odometer reading) for current month subtracted from previous month.
column: lastOdo
date column: fueltrandate
table: fueltran
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ringovski Hall of Fame Points: 3521 More actions · Answer 1

something like this;

Select

SUM (CASE WHEN DATEPART(m, [FuelTranDate]) = DATEPART(m,GETDATE()) THEN LastOdo ELSE '' END)

- SUM(CASE WHEN DATEPART(m, [FuelTranDate]) = DATEPART(m, DATEADD(m, -1, GETDATE())) THEN Lastodo ELSE '' END )) as [Odo]

FROM [FPNZ].[tblFuelTran]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

You can also use the LEAD or LAG functions to access the next and previous row values respectively

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Thom A SSC Guru Points: 99133 More actions · Answer 3

As Kingston said, Lag/Lead, Analytic Functions (Transact-SQL), would be a much better choice.

Without knowing your full DDL or data sample, this is complete gueswork, but:

USE DevTestDB;

GO

CREATE TABLE #fueltran (ReadingID INT IDENTITY(1,1),

OdoReading INT, --No idea what an OD reading actualyl looks like,

fueltrandate datetime2(0));

INSERT INTO #fueltran (OdoReading, fueltrandate)

VALUES (12, '20-Nov-2016'),

(15, '21-Nov-2016'),

(11, '22-Nov-2016'),

(19, '23-Nov-2016'),

(7, '24-Nov-2016');

SELECT fueltrandate,

OdoReading,

LAG(OdoReading) OVER(ORDER BY fueltrandate ASC) AS LastOdo,

OdoReading - LAG(OdoReading) OVER(ORDER BY fueltrandate ASC) AS OdoChange

FROM #fueltran;

DROP TABLE #fueltran;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Joe Celko SSCertifiable Points: 5749 More actions · Answer 4

In the future, would you please follow forum rules and post DDL along with sample data? Depending on what your data actually looks like, you will need to use some form of lead () or lag (). Hey, if you are not going to post DDL, why should we post DML and do all this design work for you?

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

MMartin1 One Orange Chip Points: 27799 More actions · Answer 5

I think you may have to keep track of the vehicle as well?

If you dont mind doing it the old fashion way (pre Lag() and Lead() ) here is a sample that works >

CREATE TABLE #fueltran (

vehicleID INT,

OdoReading INT,

fueltrandate datetime2(0)

);

INSERT INTO #fueltran

VALUES (1,12, '20-Nov-2016'),

(1,15, '21-Nov-2016'),

(1,20, '22-Nov-2016'),

(1,29, '23-Nov-2016'),

(1, 37, '24-Nov-2016'),

(2, 77, '21-Nov-2016'),

(2, 84, '22-Nov-2016'),

(2, 93, '23-Nov-2016')

;

WITH logCTE as

(

select row_number() over (partition by vehicleID

order by fuelTranDate ASC) as dataLogNumber

,*

from #fuelTran

)

SELECT t1.vehicleID,

t1.fuelTranDate,

t1.odoReading,

t1.odoReading-t2.odoReading as MilesSinceLastLog

FROM logCTE as t1 left join logCTE as t2

Ont1.vehicleID = t2.VehicleID

andt1.dataLogNumber= t2.dataLogNumber+1

;

DROP TABLE #fueltran;

----------------------------------------------------

MMartin1 One Orange Chip Points: 27799 More actions · Answer 6

I am also assuming that the odoReading (mileage) increases with time , we are talking about vehicle miles correct?

----------------------------------------------------