This query gets the expected results you listed for project Z.123
DECLARE @delta INT = DATEPART(YY, GETDATE()) - CAST(SUBSTRING(@Year1, 4, 4) AS INT)
;WITH q AS
(
SELECT 'Supposed' AS Period, ProjectNr,
CASE WHEN @delta = 0 THEN curyr
WHEN @delta = 1 THEN yr1
WHEN @delta = 2 THEN yr2
WHEN @delta = 3 THEN yr3
ELSE yr4 END as Val
FROM Test
WHERE [Year] = @Year1
UNION
SELECT 'Current', ProjectNr, curyr FROM Test
WHERE [Year] = @Year2
)
SELECT A.ProjectNr, A.Val as [Current], B.Val as [Supposed]
FROM q A
INNER JOIN q B ON A.ProjectNr=B.ProjectNr AND B.Period = 'Supposed'
WHERE A.Period = 'Current'
For you first set of parameters I got these results
D.489 45 700
Z.123 25 50
and for the second example
Z.123 25 200
If this isn't what you wanted you'll have to find a better way to explain your problem