Solved

generating a week name from a date

Posted on 2009-04-08
9
1,024 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
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rsmuckles
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks again
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now