Group by and max in join. Group by should be done on fields from two different tables

Other SQL Server Topics
1

I have two tables below are the fields for each of them. They have one field in common which is uniq_id. The requirement is something like to join two tables and then do a sum a numeric amount based max DATE and group by should be done based 3 fields. Which belongs two different tables.

Table1
uniq_id round date name
AZ08252020 round1 08/25/2020 AZEK
AZ08252021 round1 08/25/2021 AZEK
AZ08252022 round5 08/25/2022 AZEK
AZ08252023 round3 08/25/2023 AZEK
AZ08252024 round3 08/25/2024 AZEK
AZ08252025 round3 08/25/2025 AZEK
BX08252020 round1 08/25/2020 BXCF
BX08252021 round1 08/25/2021 BXCF
BX08252022 round2 08/25/2022 BXCF
BX08252023 round2 08/25/2023 BXCF
BX08252024 round7 08/25/2024 BXCF
BX08252025 round7 08/25/2025 BXCF
Table2
uniq_id dept Person NAME amount date
AZ08252020 1 xyz 200 08/25/2020
AZ08252021 1 xyz 210 08/25/2021
AZ08252022 1 xyz 300 08/25/2022
AZ08252023 1 xyz 300 08/25/2023
AZ08252024 1 xyz 320 08/25/2024
AZ08252025 1 xyz 310 08/25/2025
BX08252020 2 abc 200 08/25/2020
BX08252021 2 abc 210 08/25/2021
BX08252022 2 abc 300 08/25/2022
BX08252023 2 abc 300 08/25/2023
BX08252024 2 abc 320 08/25/2024
BX08252025 2 abc 310 08/25/2025

I want to get the total amount for each dept,each round, person name(assuming one person in each dept, if more than 1 person , then we have sum that across dept)
only for the latest date. So if round1 is present two times, i would only want the most recent one.

My desired output should look like below

uniq_id round date name dept Person NAME amount
AZ08252021 round1 08/25/2021 AZEK 1 xyz 210
AZ08252022 round5 08/25/2022 AZEK 1 xyz 300
AZ08252025 round3 08/25/2025 AZEK 1 xyz 310
BX08252021 round1 08/25/2021 BXCF 2 abc 210
BX08252023 round2 08/25/2023 BXCF 2 abc 300
BX08252025 round7 08/25/2025 BXCF 2 abc 310
2

In future, please post consumable test data with dates in ISO format:

CREATE TABLE #t1
(
	uniq_id varchar(20) NOT NULL PRIMARY KEY
	,[round] varchar(20) NOT NULL
	,[date] date NOT NULL
	,[name] varchar(20) NOT NULL
);
INSERT INTO #t1
VALUES ('AZ08252020', 'round1', '20200825', 'AZEK')
	,('AZ08252021', 'round1', '20210825', 'AZEK')
	,('AZ08252022', 'round5', '20220825', 'AZEK')
	,('AZ08252023', 'round3', '20230825', 'AZEK')
	,('AZ08252024', 'round3', '20240825', 'AZEK')
	,('AZ08252025', 'round3', '20250825', 'AZEK')
	,('BX08252020', 'round1', '20200825', 'BXCF')
	,('BX08252021', 'round1', '20210825', 'BXCF')
	,('BX08252022', 'round2', '20220825', 'BXCF')
	,('BX08252023', 'round2', '20230825', 'BXCF')
	,('BX08252024', 'round7', '20240825', 'BXCF')
	,('BX08252025', 'round7', '20250825', 'BXCF');
GO
CREATE TABLE #t2
(
	uniq_id varchar(20) NOT NULL PRIMARY KEY
	,dept int NOT NULL
	,PersonName varchar(20) NOT NULL
	,amount int NOT NULL
	,[date] date NOT NULL
);
INSERT INTO #t2
VALUES ('AZ08252020', 1, 'xyz', 200, '20200825')
	,('AZ08252021', 1, 'xyz', 210, '20210825')
	,('AZ08252022', 1, 'xyz', 300, '20220825')
	,('AZ08252023', 1, 'xyz', 300, '20230825')
	,('AZ08252024', 1, 'xyz', 320, '20240825')
	,('AZ08252025', 1, 'xyz', 310, '20250825')
	,('BX08252020', 2, 'abc', 200, '20200825')
	,('BX08252021', 2, 'abc', 210, '20210825')
	,('BX08252022', 2, 'abc', 300, '20220825')
	,('BX08252023', 2, 'abc', 300, '20230825')
	,('BX08252024', 2, 'abc', 320, '20240825')
	,('BX08252025', 2, 'abc', 310, '20250825');
GO

Your question is not clear but the following produces the desired results:

WITH Results
AS
(
	SELECT T1.uniq_id, T1.[round], t1.[date], T1.[name]
		,T2.dept, T2.PersonName, T2.amount
		,RANK() OVER (PARTITION BY T2.dept, T1.[round] ORDER BY t1.[date] DESC) AS rn
	FROM #t1 T1
		JOIN #t2 T2
			ON T1.uniq_id = T2.uniq_id
)
SELECT uniq_id, [round], [date], [name], dept, PersonName, amount
FROM Results
WHERE rn = 1
ORDER BY [name], [date];
1 Like