June 14, 2017 at 7:23 am
I'm trying to create logic that can accommodate the request below. The fiscal year starts on Jan 1st, and ends on Dec 31st.
1- Week 1 always starts on 1/1/yyyy and week 52 or 53 (in some cases) always ends on 12/31/yyyy.
2- Monday is the first day of the fiscal week; however, if Jan 1 is a Monday or Tuesday, then Week 2 starts on the next Monday.
3- If Jan 1 is a Wed, Thu, Fri, or Sat, then Week 2 starts on the second Monday of January4- The first and last week of each fiscal year is always different. Some years it will be 9 days long and some years it may be 4 days long. Each week in between always starts on Monday
Any suggestions will be greatly appreciated. Thank you.
L
June 14, 2017 at 8:03 am
ldeassis09 - Wednesday, June 14, 2017 7:23 AMI'm trying to create logic that can accommodate the request below. The fiscal year starts on Jan 1st, and ends on Dec 31st.1- Week 1 always starts on 1/1/yyyy and week 52 or 53 (in some cases) always ends on 12/31/yyyy.
2- Monday is the first day of the fiscal week; however, if Jan 1 is a Monday or Tuesday, then Week 2 starts on the next Monday.
3- If Jan 1 is a Wed, Thu, Fri, or Sat, then Week 2 starts on the second Monday of January4- The first and last week of each fiscal year is always different. Some years it will be 9 days long and some years it may be 4 days long. Each week in between always starts on MondayAny suggestions will be greatly appreciated. Thank you.
L
Sounds like you need a Calendar Table. You'll find many links on the topic if you search, but here is one to get you started.
Once you have this built to your requirements, all you need to do is join to it from your existing tables and all the bucketing work is done for you.
June 14, 2017 at 8:10 am
Appreciate it. Thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply