Brian J. Parker (10/31/2014)

---

lduvall (10/31/2014)

---

This script is very helpful and it's close to what I need but not quite. I need the week# and month# to be built off the startdate parameter.

So, my client's cycle start date is 9/16/14.

This means Wk# 1 runs 9/14/14 - 9/20/14. With your table, I get the Wk# based on Calendar Year. (38 in this case)

Any suggestions for how I can make this work for me?

I assume you mean the calendar_month and week_of_year fields? Although it might make sense to add additional fields with names like cycle_month and week_of_cycle or something...

How would the cycle_month work? Like, for 9/14/14, I figure the month is 1. But for 9/13/14, would that be month 1 or month 12?

Anyway, here's a start:

DECLARE @cycle_start_month int = 9

DECLARE @cycle_start_day int = 16

SELECT calendar_date, calendar_month, week_of_year

, cycle_month = (

CASE WHEN u.calendar_month - @cycle_start_month > 0

THEN u.calendar_month - @cycle_start_month

ELSE u.calendar_month - @cycle_start_month + 12

END)

, week_of_cycle = (

CASE WHEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1 > 0

THEN DATEDIFF(week, CAST(ltrim(str(calendar_year))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1

ELSE DATEDIFF(week, CAST(ltrim(str(calendar_year-1))+'-'+ltrim(str(@cycle_start_month))+'-'+ltrim(str(@cycle_start_day)) AS datetime), calendar_date)+1

END)

FROM dbo.UTIL_DateCalendar u