SQLTeam.com | Weblogs | Forums

How To Get last record of date and one day after without filter date

#1

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
2

0 Likes

#2

What does your source data look like?

1 Like

#3

Hello Lewie, the table has 5 column :slight_smile:
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

0 Likes

#4

Have you tried looking at the LAG command?
Is row 10 (ID 82) showing the correct value for LastReading?

1 Like

#5

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
:slight_smile:
:slight_smile:

1 Like

#6

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
1 Like

#7

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

1 Like

#8

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 :smiley:

Have a Nice Day

0 Likes