For this type of scenario, I would look at maybe implementing a calendar/tally table that holds all the necessary information against each day.

My own calendar table stores (for each date) day number in year, fiscal month, fiscal year and other information relevant to the business. It's then just a simple case of linking the date in your transaction table to the calendar table to retrieve the columns required.

We've expanded the calendar table recently to include such delights as: is a working day,is a national holiday, time sheet start day etc.

There's lots of scripts around to generate these tables. SQLServerCentral.com and Google is your friend!