January 22, 2006 at 4:49 pm
I'm starting with a normal date table:
Date_ID int, (in yyyymmdd format so for example today's entry is 20050122)
date_field datetime,
day_of_week varchar(8),
day_number_in_year int,
week_number_in_year int,
year_number int,
first_date_of_week datetime,
day_number_in_week int
I'd like to add a column to reflect a data warehouse load cycle that runs Thursday through Wednesday. So new column "load_week int" would begin with 1 on Jan 5, 2006 and run through the 53rd week ending Jan 3rd, 2007. Going back in time, Jan 1 to 4 of 2006 would be the end of the last load week of 2005.
At first it looked easy since there's week_number_in_year and set load_week = week_of_year for Thu, Fri, Sat works in most cases. But when the year starts with Fri, Sat, or Sun then that's week 1 on the calendar so the next week needs to be week_number_in_year -1.
There are other oddities such as some years spill over to 53 or 54 calendar weeks when they start on Sunday and end on Monday so a blanket statement can't be made that the first days of the year that are less than Thursday in load_week 52 of the prior.
Can anyone think of a way to handle this with t-sql using sets that isn't hopelessly convoluted? Sure, I could bash out something with a cursor that could handle this, but that's no fun and makes a lousy t-sql learning experience. The date table in question runs 1990 to 2010 and so covers quite a few oddball year-end and year-start situations when trying to calculate the load_week entry.
Thanks for any help or pointers.
January 22, 2006 at 6:10 pm
Not sure that this will solve your problem, but you can use the 'SET DATEFIRST' T-SQL command, for example:
--store the current datefirst setting
declare @datefirst int
select @datefirst = @@datefirst
--reset the datefirst setting to Thursday
set datefirst 4
--return the load_week for a number of sample dates
select datepart(week,'20060105')-1
select datepart(week,'20060111')-1
select datepart(week,'20060112')-1
--restore the datefirst setting
set datefirst @datefirst
January 23, 2006 at 12:41 pm
Thanks, using:
set
datefirst 4
update dim_date set load_week = datepart(week,date_field) - 1
Followed by:
update
a
set
load_week = (select max(load_week) from dim_date b where b.year_number = a.year_number -1) from dim_date a where load_week = 0
Works for most years except when Thursday is the first day of the year. Years where Thurday is first results in starting with week 0 which then gets overwritten with the prior year's max. So I'm still working on how to express 'don't use the -1 when the first day of the year = Thursday' in t-sql syntax.
Any more ideas? Thanks for the help so far!
January 23, 2006 at 1:05 pm
In case anyone is interested, the solution turned out to be:
declare
@datefirst int
select
@datefirst = @@datefirst
set
datefirst 4
update
a set load_week = datepart(week,date_field) - 1 from dim_date a where year_number not in (select year_number from dim_date b where month_number_in_year = 1 and day_number_in_year = 1 and day_of_week = 'Thursday')
update
a set load_week = datepart(week,date_field) from dim_date a where load_week is NULL set datefirst @datefirst
update
a set load_week = (select max(load_week) from dim_date b where b.year_number = a.year_number -1) from dim_date a where load_week = 0
Thanks Paul for pointing me in the right direction!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply