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

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

What does your source data look like?

1 Like

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

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

1 Like

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

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

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

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