question

ldeassis avatar image
ldeassis asked

Create custom weeks for a set year

I've searched the list of questions, but the closest I've found to my question was a post by Tom Staab regarding custom weeks. I'm trying to create the following logic for a set fiscal year (01/01/yyyy thru 12/31/yyyy): a) January 1 is always the beginning of week 1. b) Monday is the first day of the fiscal week. c) If Jan 1 is a Monday or Tuesday, the next week starts on next Monday. d) If Jan 1 is Wednesday or later, the next week starts on the second Monday I was able to come up with Wk_2_Beg date using the code below. Is there a better way to create all "custom weeks" and week numbers for the set year; i.e. 2016, without having to create each new week individually based on week 1 starting date/day of the week? Appreciate it. Thanks SET DATEFIRST 1 DECLARE @StartDate DATE = '01/01/2016'; -- I'm omitting the code for creating table #Week CREATE TABLE #WK_NEW ( [date] datetime, [year] int, month_no int, day_of_week varchar(10), week_beg datetime, week_end datetime, week_no int, wk_01_beg datetime, wk_02_beg datetime ); insert #WK_NEW SELECT [date], [year], month_no, day_of_week, week_beg, week_end, week_no, CASE WHEN week_no = 1 THEN week_beg ELSE @StartDate END AS wk_01_beg, CASE WHEN (month_no = 1 AND week_no =1 AND day_of_week = 'Monday') THEN DATEADD(dd, 7, @StartDate) WHEN (month_no = 1 AND week_no =1 AND day_of_week = 'Tuesday') THEN DATEADD(dd, 6, @StartDate) WHEN (month_no = 1 AND week_no =1 AND day_of_week= 'Wednesday') THEN DATEADD(dd, 11, @StartDate) -- WHEN (month_no = 1 AND week_no =1 AND day_of_week = 'Thursday') THEN DATEADD(dd, 10, @StartDate) WHEN (month_no = 1 AND week_no =1 AND day_of_week = 'Friday') THEN DATEADD(dd, 9, @StartDate) WHEN (month_no = 1 AND week_no =1 AND day_of_week = 'Saturday') THEN DATEADD(dd, 8, @StartDate) ELSE DATEADD(dd, 7, @StartDate) END AS wk_02_beg FROM #WEEK
weeks
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.