calculating weeks in a month

[damzi]

Hello Gurus ! I have a date field in my database that gives me the month and year. I have to calculate the number of weeks for the respective months using this. Can anyone help me with this please. Thanks, D

 

[DoubleD]

It all depends if you're trying to calculate the number of calendar weeks, or 7 day periods.

I am what I am based on the decisions I have made.

DoubleD

 

[damzi]

I mind sound silly - but what wld be the difference in the two ?

 

[DoubleD]

Let's take November of 2004.
Nov 1 is a Monday. So based on Calendar, week 1 for November would consist of only Nov 1 - 6. In this example, week 5 of Nov would be Nov 28-30, only 3 days.

A true seven day week would either include October 31 in week 1, or be Nov 1 - 7, which includes two Mondays. In this example, week 5 would be Nov 29-30, only 2 days, or be excluded.

I am what I am based on the decisions I have made.

DoubleD

 

[yksvaan]

All of the world does not start the week on Sunday. Europe uses ISO 8601 standard where the week starts on Monday and week is on that calendar period where most of the weekdays are (so January 4 is always on week 1). You can find good information about week definitions here:

http://www.merlyn.demon.co.uk/weekinfo.htm

I agree one must realize what definition to use. Sorry, damzi, I dont have solution as a calculation, but it shouldnt be too hard to code one. Another solution is that if you are using PowerPlay, and if you have calendar table in your database, you could produce query that gives at least a dummy value for every week of your time span. Then you can make measure of type Category Count of the time-dimension's week level, with the dummy value acting as Activity measure. Transformer supports many kind of week definitions.

Cheers

 

[yksvaan]

Here is how to calculate number of weeks in a month according to ISO 8601. I present it both in SQL Server syntax and VBScript. Haven't run large tests but checked some years.

Also, the second part shows how to get date's ISO week number in one line(no recursion), which is missing from SQL Server (Oracle has it).

Warning: if your language has the datetime data type as seconds(or hours..) be careful, because you must check how summer time affects subtractions. Both SQL and VBS have day as the integer part.

I can give explanations about these statements if you need.

Cheers

Number of ISO weeks:
-- SQL Server mindate Monday 1753-01-01 doesn't really matter
--
declare @theFirstDOMonth datetime
set nocount on
SET @theFirstDOMonth='1996-02'+'-01'
--select DATEADD(mm,1,@theFirstDOMonth) - 1 - ((7+((CONVERT(int,DATEADD(mm,1,@theFirstDOMonth)-4)-0)%7))%7) as LastThursday
select 1 + FLOOR(DATEDIFF(dd,@theFirstDOMonth,
DATEADD(mm,1,@theFirstDOMonth) - 1 - ((7+((CONVERT(int,DATEADD(mm,1,@theFirstDOMonth)-4)-0)%7))%7)) / 7 )as isoweeksinmonth

/* VBScript
Function Main()

dim theFirstDOMonth
theFirstDOMonth=DateValue( "1996-02"&"-01" )
msgbox "Number of weeks in that month " & 1+ Int( ( DateAdd( "M" , 1 , theFirstDOMonth ) - Weekday( DateAdd( "M" , 1 , theFirstDOMonth ) - 4, 2) - theFirstDOMonth ) / 7 )
Main = DTSTaskExecResult_Success
End Function
*/

ISO Week number:
--
declare @theDate datetime
set nocount on
SET @theDate='1900-01-01'
select DATEPART(yy,@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)) as isowyear
select 1+((7+((CONVERT(int,@theDate)-0)%7))%7) as isowday
select 1+FLOOR(DATEDIFF(dd,CONVERT(datetime,STR(DATEPART(yy,@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)))+'-01-01'),(@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)))/7) as isoweek


/* VBScript
Function Main()

dim theDate
theDate=DateValue( "2005-01-01" )
msgbox "ISO Week Year is " & Year( theDate + 4 - Weekday( theDate, 2 ) ) &Chr(13)&_
"ISO Week Day is " & Weekday( theDate, 2 ) &Chr(13)&_
"ISO Week is " & 1 + Int( ( ( theDate + 4 - Weekday( theDate, 2 ) ) - DateSerial( Year( theDate + 4 - Weekday( theDate, 2 ) ), 1, 1) ) / 7 )
Main = DTSTaskExecResult_Success
End Function
*/

 

[yksvaan]

And you can do this solely in Transformer datasources making no of weeks Calculated column, here "date" refers to column which has yyyymm or yyyymm01:

1 + floor ( ( date-to-days-from-1900 ( add-months ( "date" , 1 ) ) - 1 - mod ( date-to-days-from-1900 ( add-months ( "date" , 1 ) ) - 5 , 7 ) - date-to-days-from-1900 ( "date" ) ) / 7 )

Cheers