Hi satabeach,
Refer below query.
SQL
DECLARE @Test AS TABLE(Rid INT,MeterNo INT,Reading INT,ReadDate VARCHAR(15))
INSERT INTO @Test VALUES(1,5,450,'25-Dec-2020')
INSERT INTO @Test VALUES(2,4,200,'25-Dec-2020')
INSERT INTO @Test VALUES(3,10,400,'25-Dec-2020')
INSERT INTO @Test VALUES(4,5,500,'27-Jan-2021')
INSERT INTO @Test VALUES(5,10,478,'27-Jan-2021')
INSERT INTO @Test VALUES(6,4,320,'27-Jan-2021')
INSERT INTO @Test VALUES(7,5,600,'25-Feb-2021')
INSERT INTO @Test VALUES(8,4,400,'25-Feb-2021')
INSERT INTO @Test VALUES(9,10,530,'25-Feb-2021')
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY (MeterNo) ORDER BY (ReadDate) DESC) Row_Num,*
FROM @Test
)
SELECT MeterNo,
(SELECT Reading FROM CTE c2 WHERE Row_Num = 1 AND c1.MeterNo = c2.MeterNo) Prev_Reading,
(SELECT ReadDate FROM CTE c2 WHERE Row_Num = 1 AND c1.MeterNo = c2.MeterNo) Prev_Date,
(SELECT Reading FROM CTE c2 WHERE Row_Num = 2 AND c1.MeterNo = c2.MeterNo) Last_Reading,
(SELECT ReadDate FROM CTE c2 WHERE Row_Num = 2 AND c1.MeterNo = c2.MeterNo) Last_Date
FROM CTE c1
WHERE Row_Num = 1
Output
| MeterNo |
Prev_Reading |
Prev_Date |
Last_Reading |
Last_Date |
| 4 |
320 |
27-Jan-21 |
400 |
25-Feb-21 |
| 5 |
500 |
27-Jan-21 |
600 |
25-Feb-21 |
| 10 |
478 |
27-Jan-21 |
530 |
25-Feb-21 |
