January 29, 2008 at 2:30 pm
I have a dates table in a datawarehouse that has
Date, Day, Month, Year, Quarter but missing Weeks in a month.
I am having the most difficult of time writing the code for this.
Does any one have something I can use, modify that will calculate the weeks in a month?
Thanks
January 29, 2008 at 7:40 pm
January 29, 2008 at 8:14 pm
I think you need to provide a definition of what you mean by "Weeks in a month" before anyone can really help you with code to produce that. "Weeks in a month" is a term that probably is specific to your organization that may not be the same as what another organization calls it.
The function on the link below is designed to load a date table for DW and reporting type applications.
Date Table Function F_TABLE_DATE
January 30, 2008 at 7:41 am
Weeks in a month for me mean...
Number of weeks in a month.
Example: Week 1, Week 2, Week 3, Week 4
New Month:
Week 1 to Week 4
January 30, 2008 at 8:07 am
Is it dates 01 to 07 are Week1 and 08 to 14 are Week2 like that. If yes then
/* To produce sample data */
SELECTIDENTITY(int, 1,1) AS ID_Num,
myDate = CONVERT(DATETIME, '01/30/2007'),
WeekInMonth = CONVERT(TINYINT, 0)
INTO #myDates
FROM sysobjects
UPDATE D
SET
myDate = DATEADD(DAY, 1 - ID_NUM, myDate)
FROM
#myDates D
/* Statement to set WeekInMonth */
UPDATE D
SET
WeekInMonth = (CONVERT(INT, DATEPART(DAY, myDate) - 1) / 7) + 1
FROM
#myDates D
SELECT * FROM #myDates
ORDER BY 1
Regards,
gova
January 30, 2008 at 12:25 pm
JonJon (1/30/2008)
Weeks in a month for me mean...Number of weeks in a month.
Example: Week 1, Week 2, Week 3, Week 4
New Month:
Week 1 to Week 4
That tells absolutely nothing about the definition of weeks in a month.
Does Week 1 start on day 1 and run to day 7, or do the weeks run Monday to Sunday, Sunday to Saturday, etc.? Maybe the weeks follow the ISO standards? Maybe they have another definition?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply