select
b.assessment_date,
a.date_month
from
calendar a
outer apply
(
select top (1) b.assessment_date
from assessment_table b
where b.assessment_date <= a.date_month
order by b.assessment_date desc
)b
select *
from CalendarTable f
INNER JOIN assessment_table t
ON t.assessment_date = (SELECT TOP 1 assessment_date
FROM assessment_table
WHERE assessment_date<=f.[Date]
ORDER BY assessment_date DESC)
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
Creating a row for each month
Posted - 01/24/2013 : 11:26:35
