I am trying to create a fiscal calendar in which the fiscal year starts July 1 and a week is defined as Monday to Sunday.
But for example; if the 1st day in a week in a month is a Saturday, then Saturday to Sunday will be seen as 1 week in that month and the new week starts on Monday and ends on Sunday and so on.
See sample of the table I want to create below:
- Period refers to the months of the fiscal year.
- Week is number of weeks in that month.
- Start date(week start date) is the day the week began
- End date(week end date) is the day the week ended.
- Day of week is the dates between the start date and end date.
- Year
I am thinking that I need a procedure that maybe takes the first day of the fiscal year then iterates through all the days of the year adding the columns start and end date, week number, period and year the day belongs to.