number weeks from April to September for every year

Question

I want to number weeks from April to September for every year, such that week 1 starts 1st of April and ends the following Saturday and week 2 starts first Sunday of April and ends the following Saturday and so on.... till the end of September. How do I do that using TSQL.

Thanks in advance.

Answer 1

You already have a calendar table, great!

The you don't the table of numbers I talked about - the calendar table is an extension of that concept.

I think this should work:

SET DATEFIRST 7 
UPDATE CalendarTable
SET    NewWeekCol = datepart(week, CalendarDate) - 
                    datepart(week, datename(YEAR, CalendarDate) + '0401') + 1
WHERE  month(CalendarDate) BETWEEN 4 AND 9

But note that since I don't your table, I cannot test. But you could tweak it on your own.

Answer 2

To that end you would need a table of numbers. This is a concept that I discuss in this (relatively) short article on my web site: http://www.sommarskog.se/Short%20Stories/table-of-numbers.html

I don't give any query, since you did not give any table to work with,

Answer 3

Thank You ErlandSommarskog .
That works like a charm.