First, Cudo's to Google Chrome. I just started typing this, then lost power. When I rebooted and opened Chrome my comment was still here.:w00t:

Below is a better way to create the sample data. The technique uses a tally table; see this article[/url] by Jeff Moden for more details. Among other things the tally table will help you produce sample data faster and with cleaner code.

I also tweaked to Nevyn's solution so that it will produce the correct answer (Nevyn's solution is very good but I think it should include WHERE rownum=1.)

-- use this as a base date (so you don't have to keep calculating the date

DECLARE @startdate date=getdate();

-- populate dimDate

-- TOP(13) used for this example

WITH iTally(n) AS

(SELECT TOP(13) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1

FROM sys.all_columns)

INSERT dbo.DimDate

SELECT dateadd(day,n,@startdate) AS datevalue

FROM iTally;

-- populate dimDate

WITH TotalsData_prep AS

(SELECT datevalue, totalvalue

FROM (VALUES (3,55),(7,66),(9,77),(11,88)) t(datevalue,totalvalue))

INSERT dbo.TotalsData

SELECTdateadd(day,datevalue,@startdate) AS datevalue,

totalvalue

FROM TotalsData_prep;

--SELECT * FROM dbo.DimDate;

--SELECT * FROM dbo.TotalsData;

SELECTdatevalue,

totalvalue

FROM

(

SELECT

ROW_NUMBER() OVER (PARTITION BY d.datevalue ORDER BY t.datevalue DESC) AS rownum,

d.datevalue,

COALESCE(t.totalvalue,0) AS totalvalue

FROM dimdate d

LEFT JOIN totalsdata t ON d.datevalue >= t.datevalue

) derived

WHERE rownum=1;

Edit: Type in code comments