Fill a week days in a table with preceding Sundays value

Question

I need a table that displays the preceding Sundays value for the days in the week. The table is built from another table which has values for every day. I have day of week (as a number) and month included in the table. I'm using SQL Server 2012.

Sun 100

Mon 100

Tues 100

Wed 100

Thurs 100

Fri 100

Sat 100

Sun 300

Mon 300

Tues 300

Thanks I

Answer 1

Yes I have day of the week as a number, from a date dimension table.

I managed to to do it, by joining my fact to the date dimension table and filtering on Sunday. Then joining my date dimension back to this newly created table. So i had values for Sunday and nulls for the rest of the week. I then used a case statement with a lag:

(case when sum(b.Delivery_Value) is null then lag(sum(b.Delivery_Value),a.day_of_week - 1) over (order by a.date_key asc) else sum(b.Delivery_Value) end)

Basically subtracting one from the day of the week so the lag always goes back to Sunday.

Not sure if its the best way.