SELECT 
	
	/* 
	FROM
	Ref = Reference period (2015-2019) average age-specific death rates
	JOIN
	D = Population estimates and actual deaths for 2021-2023 
	*/
	
	D.CountryCode,					
	D.Yr, 
	D.Week, 
	
	D.DTotal AS Actual,

	(Ref.R0*D.Pop0) + (Ref.R15*D.Pop15) + (Ref.R65*D.Pop65) + (Ref.R75*D.Pop75) + (Ref.R85*D.Pop85) AS Projected
	
	FROM
	(
	SELECT -- Average age-specific rates for reference period 2015-2019

	CountryCode, Week,

	Avg(R0_14) AS R0,
	Avg(R15_64) AS R15,
	Avg(R65_74) AS R65,
	Avg(R75_84) AS R75,
	Avg(R85p) AS R85


	FROM Output2_11_2023	-- The flat file downloaded from https://mortality.org/File/GetDocument/Public/STMF/Outputs/stmf.csv 

	WHERE Sex='b' AND Yr > '2014' AND Yr < '2020'

	GROUP BY CountryCode, Week
	)

	Ref	

	RIGHT JOIN

	(
	SELECT -- Weekly deaths and population estimates for 2021-2023 

	CountryCode, Week, Yr, 

	D0_14/NULLIF(R0_14,0) AS Pop0,
	D15_64/NULLIF(R15_64,0) AS Pop15,
	D65_74/NULLIF(R65_74,0) AS Pop65,
	D75_84/NULLIF(R75_84,0) AS Pop75,
	D85p/NULLIF(R85p,0) AS Pop85,

	DTotal 
	
	FROM Output2_11_2023	-- The flat file downloaded from https://mortality.org/File/GetDocument/Public/STMF/Outputs/stmf.csv 

	WHERE Sex='b' AND Yr > '2020' 
	
	)

	D

	ON (Ref.CountryCode = D.CountryCode) AND (Ref.Week = D.Week)

	ORDER BY D.CountryCode, D.Yr, D.Week