Calendar starting on the 1st with partial weeks

[Anonymous]

My team is working on some allocation numbers and I haven't been able to figure out how this calendar build should work.  Each month/week grouping should start on the 1st of the month and count in 7 day (or less) increments until the end of each month.  With this information, I will be able to allocate how many people need to be available per week and per month without having to worry about roll-overs between months.

Week Groupings:

Jul 1-7

Jul 8-14

Jul 15-21

Jul 22-28

Jul 29-31

Aug 1-7

Aug 8-14

Aug 15-21

Aug 22-28

Aug 29-31

Sept 1-7

Sept 8-14

Sept 15-21

Sept 22-28

Sept 29-30

Oct 1-7

Oct 8-14

Oct 15-21

Oct 22-28

Oct 29-31

I just can't figure out how to do the extra group of leftover days.  Would it be a good place to start to group the day numbers to assign MonthName, Week1, Week2, Week3, Week4, Week5?

Thank you

[qliksus · Accepted solution]

As you want to group within the month without crossover the below logic should work

LOAD Date, month(Date), ceil(day(Date)/7) as week

From table ;

[Anonymous]

Thank you Susant,

This worked for me:

ceil(Day(Date(D))/7 as WeekNum