Select last two months record from each group in SQL Server

satabeach
 
on Jun 15, 2021 09:38 AM
388 Views

How to select the previous month reading with last month in SQL

Here is how my table looks

Rid MeterNo Reading ReadDate
1 5 500 27-Jan-2021
2 10 478 27-Jan-2021
3 4 320 27-Jan-2021
4 5 600 25-Feb-2021
5 4 400 25-Feb-2021
6 10 530 25-Feb-2021

below is the expected result for meter number 4 

MeterNo Prev_Reading Prev_Date Last_Reading Last_Date
4 320 27-Jan-2021 400 25-Feb-2021

I am using SQL server 2005

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 16, 2021 02:13 AM

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