Solved

generating a week name from a date

Posted on 2009-04-08
9
1,042 Views
Last Modified: 2012-06-27
Greetings -

I'm using the code below to generate a date dimension table using a set of variables input by the user.  The code fills in the range of dates and also provides useful dimensions like the weekday, etc.  The only part that's not working for me uniformly is the week name.  The name is supposed to be the Monday of that week, with the week starting Sunday, like this:

2009-03-22 Week of 3/23/2009      Sunday
2009-03-23 Week of 3/23/2009      Monday
2009-03-24 Week of 3/23/2009      Tuesday
2009-03-25 Week of 3/23/2009      Wednesday
2009-03-26 Week of 3/23/2009      Thursday
2009-03-27 Week of 3/23/2009      Friday
2009-03-28 Week of 3/23/2009      Saturday
2009-03-29 Week of 3/30/2009      Sunday

and so on.  The code works fine except for weeks which begin in the previous month . . . for these my result is like this:

2009-03-29 Week of 3/30/2009      Sunday
2009-03-30 Week of 3/30/2009      Monday
2009-03-31 Week of 3/30/2009      Tuesday
2009-04-01 Week of 4/30/2009      Wednesday
2009-04-02 Week of 4/30/2009      Thursday
2009-04-03 Week of 4/30/2009      Friday
2009-04-04 Week of 4/30/2009      Saturday

The Week Name for the last four records above should be 'Week of 3/30/2009'

Is there a way to get the last week of the month to describe the week accurately?
UPDATE newseason
SET
	YearNumber = left(@SeasonNumber,4), 
	YearName = left(@SeasonNumber,4),
	SeasonNumber = @SeasonNumber,
	SeasonNumber1 = @SeasonNumber,
	SeasonName = @SeasonName,
	MonthNumber = left(@SeasonNumber,4) + '0' + left(CONVERT(CHAR(8),TID,10),2),
	MonthName = DATENAME(MONTH,TID) ,
 
		WeekName =  REPLACE(('Week of '
					+ left(CONVERT(CHAR(8),TID,10),2) 
					+ '/' 
					+ substring(CONVERT(CHAR(8),(SELECT DATEADD(wk, DATEDIFF(wk,0,TID), 0)),10),4,2) 
					+ '/' + cast(Year(TID) as varchar)),'Week of 0','Week of '),
 
	WeekNumber = cast(Year(TID) as varchar) + '00' + cast(DATEDIFF(week, @FiscalYear, TID-1) as varchar)+1,
 
	DayNumber = cast(Year(TID) as varchar) + '0' + cast(DATEDIFF(day, @FiscalYear, TID+1) as varchar),
 
	DayName = (SELECT DATENAME(dw, TID))

Open in new window

0
Comment
Question by:rsmuckles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24099944
It would seem as though your "when is Sunday" logic is flawed.

Take a look at this function I posted over at my site which will give you the Sunday of the week that you provide as an input parameter

http://sqlservernation.com/blogs/howtos/archive/2009/04/06/how-to-determine-the-sunday-of-a-given-week.aspx

I suspect you will have similar problems with mid-week year changes like with January 1, 2009 as well.
0
 

Author Comment

by:rsmuckles
ID: 24100667
I'm not shocked that my logic is flawed and I appreciate the example, but I'm not really sure how to apply your date function to my code.  The code I'm posting is inherited and I'm no expert at SQL date logic to begin with.  Thanks for your help -

Julia
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24100696
Well what kind of data is in your "newseason" table?

What values are in TID to start before you run the update above.  What is your goal?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rsmuckles
ID: 24100789
The newseason table is a dimension table containing information about a range of dates.  The range of dates comprises a season in which products are sold.  The fields in the table are things like fiscal week number, season name, and so on, which are used by SQL Server Analytic Services as cube parameters.  TID is a datetime field.  The strings above that begin with '@' are variables entered in an earlier part of the script by the user.  A sample row is in the code box below.  I did not include that part of the script as it's simply a set of variable declarations and values.  The only one I'm having trouble with is the week name.  My goal is to alter the following clause of the update statement:

WeekName =  REPLACE(('Week of '
                                        + left(CONVERT(CHAR(8),TID,10),2)
                                        + '/'
                                        + substring(CONVERT(CHAR(8),(SELECT DATEADD(wk, DATEDIFF(wk,0,TID), 0)),10),4,2)
                                        + '/' + cast(Year(TID) as varchar)),'Week of 0','Week of '),

in such a way that all of the dates in a week which has parts in two different months share the same week name, as in the example in my original post.
2009-03-21 00:00:00.000	2009	2009	200902	200902	Spring-2009	2009003	March	20090032	Week of 3/16/2009	20090223	Saturday

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24100826
What about this?  It will always use the date for the SUNDAY preceeding TID and format it into MM/DD/YYYY.

WeekName =  'Week of ' + convert(varchar(10), dbo.fn_SundayOfWeek(TID), 101)
0
 

Author Comment

by:rsmuckles
ID: 24100968
that's pretty close, although with the attached code it's naming all of the weeks after Saturday's date instead of Monday's, and the WeekName and Weeknumber do not correlate.  I'll try and see if I can get it to name the week after Monday instead as it is in the example.
UPDATE BF_OPS_DM.dbo.dimtime_newseason
SET
	YearNumber = left(@SeasonNumber,4), 
	YearName = left(@SeasonNumber,4),
	SeasonNumber = @SeasonNumber,
	SeasonNumber1 = @SeasonNumber,
	SeasonName = @SeasonName,
	MonthNumber = left(@SeasonNumber,4) + '0' + left(CONVERT(CHAR(8),TID,10),2),
	MonthName = DATENAME(MONTH,TID) ,
	WeekName =  'Week of ' + convert(varchar(10), dbo.fn_SundayOfWeek(TID), 101),
	WeekNumber = cast(Year(TID) as varchar) + '00' + cast(DATEDIFF(week, @FiscalYear, TID-1) as varchar)+1,
	DayNumber = cast(Year(TID) as varchar) + '0' + cast(DATEDIFF(day, @FiscalYear, TID+1) as varchar),
	DayName = (SELECT DATENAME(dw, TID))
 
select * from BF_OPS_DM.dbo.dimtime_newseason order by TID

Open in new window

0
 

Author Comment

by:rsmuckles
ID: 24101052
line 10 should be as in the code below for the business rules I have to use.

Thank you so much!

 - Julia
WeekName =  'Week of ' + convert(varchar(10), (dbo.fn_SundayOfWeek(TID-2)+1), 101)

Open in new window

0
 

Author Closing Comment

by:rsmuckles
ID: 31568159
thanks again
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24101069
The code that I posted:

WeekName =  'Week of ' + convert(varchar(10), dbo.fn_SundayOfWeek(TID), 101)

Will return the "Week Of MM/DD/YYYY" for the SUNDAY of whatever date is passed in.

Let's look at this:

cast(DATEDIFF(week, @FiscalYear, TID-1)

First of all, what value is @FiscalYear.  Also, by -1 are you hoping to subtract a week?  IF so, you need to move the -1 outside of the ().  This is because SomeDateValue-1 = ONE DAY PRIOR (24 hours) to SomeDateValue.

DATEDIFF(week, @FiscalYear, TID)-1

0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question