create table dimdate (datevalue date)

create table totalsdata (datevalue date, totalvalue int)

insert into Dimdate

values(getdate())

,(dateadd(day, 1,getdate()))

,(dateadd(day, 2,getdate()))

,(dateadd(day, 3,getdate()))

,(dateadd(day, 4,getdate()))

,(dateadd(day, 5,getdate()))

,(dateadd(day, 6,getdate()))

,(dateadd(day, 7,getdate()))

,(dateadd(day, 8,getdate()))

,(dateadd(day, 9,getdate()))

,(dateadd(day, 10,getdate()))

,(dateadd(day, 11,getdate()))

,(dateadd(day, 12,getdate()))

insert into TotalsData

values(getdate(), 1)

,(dateadd(day, 3, getdate()), 55)

,(dateadd(day, 7, getdate()), 66)

,(dateadd(day, 9, getdate()), 77)

,(dateadd(day, 11, getdate()), 88)

SELECT datevalue,totalvalue FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY d.datevalue ORDER BY t.datevalue DESC) AS rownum,d.datevalue, COALESCE(t.totalvalue,0) totalvalue

FROM dimdate d

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

) derived

drop table dimdate

drop table totalsdata