Maybe this will help. LEFT JOIN the list of DISTINCT names to the data and use ISNULL:
(take two)
DECLARE @yr INTEGER = 2014
DECLARE @mn INTEGER = 10
DECLARE @ym INTEGER = @yr*100+@mn
;
WITH src AS
(
SELECT 'John' [name] , 0.50 [value] , 11 [month] , 2014 [year]
UNION ALL
SELECT 'Mary' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
SELECT 'Pete' 'name' , 0.60 'value' , 11 'month' , 2014 'year'
UNION ALL
SELECT 'John' 'name' , 0.60 'value' , 12 'month' , 2014 'year'
UNION ALL
SELECT 'Don' 'name' , 0.50 'value' , 10 'month' , 2013 'year'
)
, lst AS
(
SELECT [name]
, [value]
, [month]
, [year]
FROM (
SELECT *
, ROW_NUMBER() OVER ( PARTITION BY [name] ORDER BY [year] DESC , [month] DESC) RN
FROM src
WHERE [year]*100+[month]<=@ym
) T
WHERE RN=1
)
, nam AS
(
SELECT DISTINCT [name] FROM src
)
SELECT A.Name
, ISNULL(B.value,0) 'value'
, ISNULL(B.[month],@mn) [month]
, ISNULL(B.[year],@yr) [year]
FROM nam A
LEFT OUTER JOIN lst B
ON A.name=B.name