i have table like this :
id int
date date
currentMeterSNID int
Reading float
how can i get reading value from last date?
The result should be like this
i have table like this :
id int
date date
currentMeterSNID int
Reading float
how can i get reading value from last date?
The result should be like this
What does your source data look like?
Hello Lewie, the table has 5 column
id, date, currentmetersnid, and reading.
I want to show
id, date, currentmetersnid, reading and add 1 new column LastReading.
which lastReading Column will get the value from Reading column with date -1
Example:
2019-03-14 20 0
2019-03-15 50 20
Have you tried looking at the LAG command?
Is row 10 (ID 82) showing the correct value for LastReading?
or you can use row number
;with cte as
(
select
row_number() Over() as rn
, columns from table
)
select * from cte a join cte b on a.rn-1 = b.rn
here a.reading is LastReading
b.reading is Reading
Hope it helps
If you're on SQL 2012+, LAG is the best approach to take:
SET DATEFORMAT DMY
CREATE TABLE #readings (
id int not null,
date date null,
currentMeterSNID int null,
reading float null
);
insert into #readings values
(81, '18/03/2019', 1, 10),
(86, '19/03/2019', 1, 60),
(91, '20/03/2019', 1, 110),
(96, '21/03/2019', 1, 160),
(82, '18/03/2019', 2, 20),
(87, '19/03/2019', 2, 70),
(92, '20/03/2019', 2, 120),
(97, '21/03/2019', 2, 170)
SELECT id, date, currentMeterSNID, reading,
lag(reading, 1, 0) over(order by currentMeterSNID, date) AS LastReading
FROM #readings
--SET DATEFORMAT MDY
and if your sample data is incorrect for row 10 (ID 82)
SELECT id, date, currentMeterSNID, reading,
lag(reading, 1, 0) over(Partition by currentMeterSNID order by currentMeterSNID, date) AS LastReading
FROM #readings
Thank You @Lewie, @harishgg1, @ScottPletcher
i got the point. i tried to use LAG and LEAD Function and it's work perfectly.
Thank you so much, i really appreciate your help
Have a Nice Day