Populate Month Work Week Number in a Date Lookup Table

I am trying to populate a column of a DateLookup table with a month work week number where the work week is Monday through Sunday. However, the trick is that I have to be able to always cram everything into 5 weeks. For example, you would end up with 6 week numbers in August 2010 (the first week would be one day long since Aug 1 is a Sunday and the sixth would be two days long). I came up with a solution that I thought worked at first, but later discovered sometimes resulted in months that were needlessly crammed into 4 weeks, which I want to avoid. Any ideas?
DECLARE @Date DATETIME, @Month5WorkWeekNumber TINYINT,
	@DateKey CHAR(2), @WeekDayName VARCHAR(10)
SET @Date = '1/1/1900'
SET @Month5WorkWeekNumber = 1
SET @DateKey = RIGHT(CONVERT(VARCHAR(8), @Date, 112),2)
SET @WeekDayName = DATENAME(dw, @Date)

WHILE @Date < '1/1/2100'
BEGIN

SET @Month5WorkWeekNumber = CASE WHEN @DateKey = '01' THEN 1
	ELSE @Month5WorkWeekNumber END

UPDATE DateLookup
SET Month5WorkWeekNumber = @Month5WorkWeekNumber
WHERE DateFull = @Date


SET @Date = DATEADD(dd, 1, @Date)
SET @Month5WorkWeekNumber =
	CASE
	WHEN @WeekDayName = 'Sunday' AND NOT @DateKey IN ('01','02')
	THEN @Month5WorkWeekNumber + 1
	ELSE @Month5WorkWeekNumber END
SET @DateKey = RIGHT(CONVERT(VARCHAR(8), @Date, 112),2)
SET @WeekDayName = DATENAME(dw, @Date)

END

Open in new window

gsszuberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blandyukCommented:
Firstly, I'd use DATEPART to get your @DateKey and specify it as a TINYINT:

DECLARE @DateKey TINYINT;
SET @DateKey = DATEPART(DAY, @Date);

Second, can't you simply use DATEPART to analyse what month it is and do your weeks from there?
0
SuperdaveCommented:
The exact problem seems logically impossible, and I haven't figured out what workaround your code is trying to accomplish, but the possibilities I can think of are:

Number weeks from the first of the month instead of from Monday.

Number the last incomplete week the same as the first incomplete week (1 or 0, depending on how you're numbering).  You could use modulo 7 in your computation.

Max out the number at 5 so the last complete week and the last partial week have the same number.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blandyukCommented:
Not sure how you @Date ties into the database but it looks like your simply trying to get a count of how many weeks are in each month? Doing this by day will be very slow. I've put something together below which does it by week, might not be exactly right but have a look:
DECLARE @Date DATETIME, @DateKey TINYINT, @Count INT, @Month INT;
-- First day of the below date is a Monday, beginning of calendar
SET @Date = '01/04/2002'

SET @Month = DATEPART(MONTH, @Date);
SET @Count = 1;

WHILE @Date < '01/01/2100'
BEGIN
	SET @Date = DATEADD(WEEK, 1, @Date);
	IF NOT DATEPART(MONTH, @Date) = @Month
	BEGIN
		UPDATE DateLookup SET Month5WorkWeekNumber = @Count WHERE DateFull = @Date;
		
		SET @Count = 1;
		SET @Month = DATEPART(MONTH, @Date);
	END
	ELSE
	BEGIN
		SET @Count = @Count + 1;
	END
END

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SuperdaveCommented:
One other possibility would be to get rid of the lookup table and design your queries or lookups to use some kind of date arithmetic to get what you want.  If you show some examples of queries or formulas using this table, maybe we can figure out a better way.

Correction to my post above I meant "modulo 5" where I said "modulo 7".  If using one-based numbers, then the calculation would be (week-1)%5+1.

0
gsszuberAuthor Commented:
blandyuk, you're right it would be more effecient to use DATEPART instead to get my @DateKey. I'm not sure how to implement your second suggestion. As far as how my code actually works, yes it goes row by agonizing row and is extremely slow. However, once this the column is populated I never have to worry about it again. I'm not worried at all about peformance here, just getting the logic to work.

Superdave, I got a headache when I was asked to do this, but we have a report that requires a maximum of a 5-week month. Basically, all my workaround does is tell @Month5WorkWeekNumber not to increment if the first or second day of the month is Sunday. So in August 2010, the first day of the month (Sunday) gets tossed in with the week 8/2/2010-8/8/2010. At first it seemed to work, but after closer analysis it left the door open to shortening certain months down to 4 weeks. I'd rather not cap the number of weeks at five either, since that carries with it the possiblity of having a one or two day week at the beginning of the month and a 8 or 9 day week at the end of the month.
0
gsszuberAuthor Commented:
I just realized that added days to the first week carries with it the same exact problems as capping the number of weeks to 5. This is the route I'm going to take.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.