November 24, 2016 at 6:20 pm
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
November 24, 2016 at 7:56 pm
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]
November 25, 2016 at 12:10 am
You can also use the LEAD or LAG functions to access the next and previous row values respectively
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 25, 2016 at 3:36 am
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
November 30, 2016 at 5:44 pm
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
December 6, 2016 at 7:32 pm
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
On t1.vehicleID = t2.VehicleID
and t1.dataLogNumber= t2.dataLogNumber+1
;
DROP TABLE #fueltran;
----------------------------------------------------
December 6, 2016 at 7:33 pm
I am also assuming that the odoReading (mileage) increases with time , we are talking about vehicle miles correct?
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply