Calcuate the number of weeks in a month

I need a query to return the # of weeks ina month for eg

June has MAY has 4 weeks, ie 5-1-07 thru 5-7-07 is week 1,

'5-6-07' and '5-10-07' is week 2 ETc.. hence i need my results to show as follows, but it needs to be automated for every month, ie the number of weeks in a month.

EG

when date between '5-1-07' and '5-5-07' then 'Week1'
when date between '5-6-07' and '5-12-07' then week2'
when date between '5-13-07' and '5-19-07' then 'week3'

I need the results, above to be automated for each month..

You should consider the use of a Calendar table. (See this reference.)

A Calendar table is a very useful support object in most databases where it is often necessary to handle date related data.

|||I do have the calendar tbl. But that does not ahve the week of the month in it. I also looked at the reference u listed, I need the week of the month, ie between 1-5, not the week of the year.|||I do have the calendar tbl. But that does not ahve the week of the month in it. I also looked at the reference u listed, I need the week of that month, ie in May we have 5 weeks, so I need it sho show as week 1, week2 etc..ie between 1-5, not the week of the year.|||

Without knowing quite how you want to use it...here's an approach to start.

Code Snippet

declare @.date datetime

set @.date =getdate()

select 1 +(W + first.first_week_nbr)as MoWeekNbr

from dbo.Calendar

innerjoin

(

select @.date as seldate, first_week_nbr

from dbo.calendar where dt =dateadd(d,-1*(day(@.date)-1), @.date)

) first

on first.seldate = @.date

where dt = @.date

|||

It is easy to add additional columns to the Calendar table for your specific needs.

If you need a 'Week of the Month', then add a column and use an UPDATE to populate the data. It's not a complex algorithm...

|||Does the first week of each month ALWAYS start on the first?|||Yes, the first week of the month, always starts on the FIRST.|||This code gives me erros such as Invalid column name 'first_week_nbr'. etc.. we dont have the same calendar table|||

IF you add a 'computed' WeekOfMonth column to your Calendar table, the following will create the proper values.

Code Snippet

ALTER TABLE Calendar
ADD WeekOfMonth AS datepart( wk, dateadd( day, 0, datediff( day, dateadd( month, datediff( month, 0, [date] ), 0 ), [date] )))

Now you can use a JOIN with the Calendar table to find the WeekOfMonth and use that value in groupings, etc.

|||

THis code does not return the rite result, for eg if the date is 2007-02-05, it falls under week 2.-BUT The code returns week1

select datepart( wk, dateadd( day, 0, datediff( day, dateadd( month, datediff( month, 0, '20070205' ), 0 ),
'20070205' )))

|||

I asked you specifically, if

Does the first week of each month ALWAYS start on the first?

.

And you replied,

Yes, the first week of the month, always starts on the FIRST.

Following that logic, the first through the 7th is in week one, 8th through 14th in week two, etc. Therefore '2007-02-05' would correctly fall in week one.

However, it seems like now your previous response may have mislead me. (And of course, I take responsibility for not asking my question in a more exacting form. I clearly see how the confusion exists.)

It appears that you may have meant that a week is from Sunday-Saturday (Calendar). And that any number of days falling within a calendar week (even if that calendar week contains the days of a different month) constitutues the 'first week of the month'.

My assumption was that a week was seven days.

Please clarify. What determines a Week? (Numerical or Calendar)

|||

select day(getdate()) / 7 as [WeekOfMonth]

,convert(char(1),day(getdate())/ 7 )+'Week'as [MonthWeek]

--

select *

,day(<DateField>)/ 7 as [WeekOfMonth]

,convert(char(1),day(<DateField>)/ 7 )+'Week'as [MonthWeek]

from <TableName>

|||

Rusag2,

Somehow, this just doesn't seem right... (using your suggested algorithms)

Code Snippet

Select
Today = getdate(),
WeekOfMonth = day(getdate()) / 7,
MonthWeek = convert(char(1),day(getdate()) / 7 ) + 'Week'

Today WeekOfMonth MonthWeek
--
2007-06-25 19:46:26.130 3 3Week

It seems like today 'should be' in the forth (or fifth) week of the month...

|||

here's another alternative, you can also make use of a calendar udf, here's my favorite calendar udf

[edited]

CREATE FUNCTION dbo.GetCalendarDates
(
@.StartDate smalldatetime
, @.EndDate smalldatetime
)
RETURNS @.CalendarDates TABLE (
Row int IDENTITY(1,1)
, CalendarDate smalldatetime
, [Day] AS day( [CalendarDate] )
, [Month] AS month( [CalendarDate] )
, [Year] AS year( [CalendarDate] )
, YearDay AS datepart( dayofyear, [CalendarDate] )
, DayOfWeek AS datepart( weekday, [CalendarDate] )
, WeekOfYear AS datepart( week, [CalendarDate] )
, DateFirst AS @.@.DATEFIRST
, WeekOfMonth int
, NumOfWeeks int
, WeekStart smalldatetime
, WeekEnd smalldatetime
)
AS
BEGIN
DECLARE @.StartOfMonth smalldatetime
DECLARE @.EndOfMonth smalldatetime

SET @.StartOfMonth = CONVERT(varchar(2),MONTH(@.StartDate)) + '/1/' + CONVERT(varchar(4),YEAR(@.StartDate))
SET @.EndOfMonth = CONVERT(varchar(2),MONTH(@.EndDate)) + '/1/' + CONVERT(varchar(4),YEAR(@.EndDate))
SET @.EndOfMonth = DATEADD(day,-1,DATEADD(month,1,@.EndOfMonth))

WHILE @.StartOfMonth <= @.EndOfMonth
BEGIN

INSERT
INTO @.CalendarDates
SELECT CONVERT(varchar(10),@.StartOfMonth,101), 1, 1, CONVERT(varchar(10),@.StartOfMonth,101), CONVERT(varchar(10),@.StartOfMonth,101)

SET @.StartOfMonth = DATEADD(d,1,@.StartOfMonth)
END

UPDATE a
SET WeekOfMonth = WeekOfYear - MinWeek
FROM @.CalendarDates a INNER JOIN
(
SELECT [Year]
, [Month]
, MIN(WeekOfYear) - 1 AS MinWeek
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]
UPDATE a
SET WeekStart = b.WeekStart
, WeekEnd = b.WeekEnd
, NumOfWeeks = b.NumOfWeeks
FROM @.CalendarDates a INNER JOIN
(
SELECT [Year]
, [Month]
, WeekOfMonth
, MIN(CalendarDate) AS WeekStart
, MAX(CalendarDate) AS WeekEnd
, COUNT(Row) AS NumOfWeeks
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
, WeekOfMonth
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]
AND a.WeekOfMonth = b.WeekOfMonth

UPDATE a
SET NumOfWeeks = b.NumOfWeeks
FROM @.CalendarDates a INNER JOIN
( SELECT [Year]
, [Month]
, Max(WeekOfMonth) AS NumOfWeeks
FROM @.CalendarDates
GROUP BY
[Year]
, [Month]
) b ON a.[Year] = b.[Year]
AND a.[Month] = b.[Month]

DELETE
FROM @.CalendarDates
WHERE CalendarDate NOT BETWEEN @.StartDate AND @.EndDate

RETURN
END

ex.

declare @.to smalldatetime
declare @.from smalldatetime
declare @.thedate smalldatetime

set @.to = '06/01/2007'
set @.from = '07/30/2007'
set @.thedate = '06/25/2007'

[edited]

select distinct @.thedate, a.WeekOfMonth, a.WeekStart, a.WeekEnd, 'Week ' + convert(varchar(1),a.WeekOfMonth)
from dbo.GetCalendarDates(@.to,@.from) a