asked
Calculate week from a date
I have to calculate a week number depending on a date for a customer. their fiscal calendar is different than the regular calendar , so I can't use the usual date to week number conversion. so in the customer_calendar table I have three columns year_nbr, wk_no , week_ending_date .How can I calculate the week number for a particular date? I need the week nbr to map to another table so I can calculate the sale per item in each week. Thanks for your help in advance!
SELECT TOP 1 wk_no FROM customer_calendar WHERE week_ending_date >= @dt AND year_nbr = YEAR(@dt) ORDER BY week_ending_date DESC Now if you have your sales in Another table, with a salesdate for each item, I would make an inline table valued function of the above code snippet and cross apply that to the salesitems table. INLINE table valued function is important here. If you make it a multistatement table valued function, you will take a serious performance hit. CREATE FUNCTION dbo.GetWeeknumberForDate(@dt datetime) RETURNS TABLE AS RETURN ( SELECT TOP 1 wk_no FROM customer_calendar WHERE week_ending_date >= @dt AND year_nbr = YEAR(@dt) ORDER BY week_ending_date DESC )
19 People are following this question.
