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
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.