SQL OUTER JOIN With Conditional

1

hello, i need help in my outer join query
i have a 3 tables like:

Table Location
id , Location
1 , T1
2 , T2
3 , T3
4 , T4
5 , T5

Table MeterSN
ID , MeterSN , LocationID
1 , 123456789 , 1
2 ,123456788 , 2
3 , 123456787 , 3
4 , 123456786 , 4
5 , 123456785 , 5
6 , 123456784 , 1
7 , 123456783 , 2
8 , 123456782 , 3
9 , 123456781 ,4
10 ,123456780 , 5

And the Last Table Record

id , RecordedDate , MeterSNID , Reading
1, 27-03-2019, 1, 20
2 , 27-03-2019 , 2 , 20
3 , 27-03-2019 , 3 , 20
4 , 27-03-2019 , 4 , 20
5 , 27-03-2019 , 5 , 20
6 , 28-03-2019 , 6 , 30
7 , 28-03-2019 , 7 , 30
8 , 28-03-2019 , 8 , 30
9 , 28-03-2019 , 9 , 30
10 , 28-03-2019 , 10 , 30
11 , 29-03-2019 , 1 , 40
12 , 29-03-2019 , 2 , 40
13 , 29-03-2019 , 3 , 40
14 ,29-03-2019 , 4 , 40
15 , 29-03-2019 , 5 , 40

Nah I want to show something like this
questiom

I want to show all Location with last reading is the value of reading in last date (if last date is not have value then its become 0, example read if there are no record in Table record in 27-03-2000 then 28-03-2000 lastreading should be 0)

and it will read base on newest meterSN in record

2

can you post the sql query you have tried?

3

Dear @Lewie,
I have Tried Something like this, but the value is not coming out like i want

USE [FlexInfrastruktur]
GO
/****** Object:  StoredProcedure [dbo].[INF_Facility_PROC_viewLastWater]    Script Date: 28/03/2019 12:53:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[INF_Facility_PROC_viewLastWater]
@Date date,
@MeterSN varchar(50)
AS
BEGIN
	IF @MeterSN = ''
	BEGIN
		with cteRowNumber AS(
			SELECT a.id, a.Name, a.Description, ISNULL(b.MeterSN,0) MeterSN, 
				ISNULL(c.CurrentMeterSNId,0) CurrentMeterSNId, c.date, ISNULL(c.Reading,'') Reading
					ROW_NUMBER() over(partition by a.id ORDER by b.date desc) as ROWNUM
				FROM INF_Facility_Location a
				LEFT JOIN
				INF_Facility_WaterMeter b ON b.LocationID = a.id
				LEFT JOIN
				INF_Facility_WaterRecord c ON c.CurrentMeterSNId = b.id AND
				(CONVERT(VARCHAR(10),c.date,120) = CONVERT(VARCHAR(10),@Date,120)
				OR
				CONVERT(VARCHAR(10),c.date,120) = CONVERT(VARCHAR(10),DATEADD(DAY,-1,@Date),120))
			
			),
			cte2RowNumber AS(
				select *, ROW_NUMBER() over(partition by CurrentMeterSNID ORDER by CurrentMeterSNID desc) as rownum2
				from cteRowNumber
				where RowNum = 1
			),
			cte3RowNumber AS(
				select *, ROW_NUMBER() over(partition by CurrentMeterSNID ORDER by CurrentMeterSNID desc) as rownum3
				from cteRowNumber
				where RowNum = 2
			)
			
				SELECT 
				b.id,b.name,b.Description, 
				ISNULL(b.MeterSN,'') MeterSN ,
				ISNULL(a.date,'') date, 

				CASE WHEN b.Reading IS NULL THEN ISNULL(a.Reading ,'')
				ELSE ISNULL(b.Reading,'') 
				END AS CurrentReading,

				CASE WHEN b.Reading IS NULL THEN 0 
				ELSE ISNULL(a.reading,'') 
				END AS LastReading,

				CASE WHEN ABS(a.reading - b.Reading) = b.Reading THEN 0
					ELSE  ISNULL(ABS(a.reading - b.Reading),0)
					END AS Usage
				FROM cte3RowNumber a 
				RIGHT JOIN cte2RowNumber b ON a.id = b.id
				ORDER by b.Name
	END
ELSE
	BEGIN
		with cteRowNumber AS(
			SELECT a.id, a.Name, a.Description, ISNULL(b.MeterSN,'') MeterSN, 
				ISNULL(c.CurrentMeterSNId,'') CurrentMeterSNId, c.date, c.Reading
					ROW_NUMBER() over(partition by a.id ORDER by c.date desc) as ROWNUM
				FROM INF_Facility_Location a
				INNER JOIN
				INF_Facility_WaterMeter b ON b.LocationID = a.id
				INNER JOIN
				INF_Facility_WaterRecord c ON c.CurrentMeterSNId = b.id AND
				(CONVERT(VARCHAR(10),c.date,120) = CONVERT(VARCHAR(10),@Date,120)
				OR
				CONVERT(VARCHAR(10),c.date,120) = CONVERT(VARCHAR(10),DATEADD(DAY,-1,@Date),120)) AND
				c.CurrentMeterSNID = @MeterSN
			),
			cte2RowNumber AS(
				select *, ROW_NUMBER() over(partition by CurrentMeterSNID ORDER by CurrentMeterSNID desc) as rownum2
				from cteRowNumber
				where RowNum = 1
			),
			cte3RowNumber AS(
				select *, ROW_NUMBER() over(partition by CurrentMeterSNID ORDER by CurrentMeterSNID desc) as rownum3
				from cteRowNumber
				where RowNum = 2
			)
				SELECT 
				ISNULL(b.id,'') id,
				ISNULL(b.name,'') name,
				ISNULL(b.Description,'') Description, 
				ISNULL(b.MeterSN,'') MeterSN ,
				ISNULL(a.date,'') date, 
				CASE WHEN a.Reading IS NULL THEN ISNULL(b.Reading ,'')
				ELSE ISNULL(a.Reading,'') 
				END AS CurrentReading,
				CASE WHEN a.Reading IS NULL THEN 0 
				ELSE ISNULL(b.reading,'') 
				END AS LastReading,
				CASE WHEN a.Reading IS NOT NULL AND b.Reading IS NOT NULL OR (a.Reading = 0 AND b.Reading =0)
					THEN ISNULL(ABS(a.reading - b.Reading),0) ELSE 0
					END AS Usage

				FROM cte3RowNumber a 
				RIGHT JOIN cte2RowNumber b ON a.id = b.id 
				order by b.Name
	END
END
4

You should have provided consumable test data.
Something like:

CREATE TABLE #Location
(
	id int NOT NULL PRIMARY KEY
	,[Location] varchar(20) NOT NULL
);
INSERT INTO #Location
VALUES (1,'T1')
	,(2,'T2')
	,(3,'T3')
	,(4,'T4')
	,(5,'T5');

CREATE TABLE #MeterSN
(
	id int NOT NULL PRIMARY KEY
	,MeterSN varchar(20) NOT NULL
	,LocationID int NOT NULL
);
INSERT INTO #MeterSN
VALUES (1,'123456789',1)
	,(2,'123456788',2)
	,(3,'123456787',3)
	,(4,'123456786',4)
	,(5,'123456785',5)
	,(6,'123456784',1)
	,(7,'123456783',2)
	,(8,'123456782',3)
	,(9,'123456781',4)
	,(10,'123456780',5);

CREATE TABLE #MeterReadings
(
	id int NOT NULL PRIMARY KEY
	,RecordedDate date NOT NULL
	,MeterSNID int NOT NULL
	,Reading int NOT NULL
);
INSERT INTO #MeterReadings
-- use iso dates!
VALUES (1,'20190327',1,20)
	,(2,'20190327',2,20)
	,(3,'20190327',3,20)
	,(4,'20190327',4,20)
	,(5,'20190327',5,20)
	,(6,'20190328',6,30)
	,(7,'20190328',7,30)
	,(8,'20190328',8,30)
	,(9,'20190328',9,30)
	,(10,'20190328',10,30)
	,(11,'20190329',1,40)
	,(12,'20190329',2,40)
	,(13,'20190329',3,40)
	,(14,'20190329',4,40)
	,(15,'20190329',5,40);

I would be inclined to use the LAG function:

WITH LatestReadings
AS
(
	SELECT MeterSNID, RecordedDate, Reading
		,LAG(Reading, 1, 0) OVER (PARTITION BY MeterSNID ORDER BY RecordedDate) AS LastReading
		,ROW_NUMBER() OVER (PARTITION BY MeterSNID ORDER BY RecordedDate DESC) AS rn
	FROM #MeterReadings
)
SELECT L.[Location], M.MeterSN, R.Reading, R.LastReading
	,R.Reading - R.LastReading AS Usage
FROM LatestReadings R
	JOIN #MeterSN M
		ON R.MeterSNID = M.id
	JOIN #Location L
		ON M.LocationID = L.id
WHERE R.rn =1;
5

hello @Ifor, i have tried your code. but there are some problem.
i only want to get 1 record per Location which is get the newest meterRecord with the last meterSN

6

Just partition the row_number() by location instead of meter:

WITH LatestReadings
AS
(
	SELECT M.LocationID, M.MeterSN, R.Reading
		,LAG(R.Reading, 1, 0) OVER (PARTITION BY R.MeterSNID ORDER BY R.RecordedDate) AS LastReading
		,ROW_NUMBER() OVER (PARTITION BY M.LocationID ORDER BY R.RecordedDate DESC) AS rn
	FROM #MeterReadings R
		JOIN #MeterSN M
			ON R.MeterSNID = M.id
)
SELECT L.[Location], R.MeterSN, R.Reading, R.LastReading
	,R.Reading - R.LastReading AS Usage
FROM LatestReadings R
	JOIN #Location L
		ON R.LocationID = L.id
WHERE R.rn =1;
1 Like