February 15, 2008 at 8:33 am
Hi everybody,I have a situation where I need to generate Financial week Numbers and add them to my new column called FinWeek on my TimeDimention Table.
The finencial weeks are arranged in th 4 4 5 parten.
January has Got 4 Financial weeks
February has got 4 '' ''
March 5 '' ''
April 4 '' ''
May 4 '' ''
June 5 '' ''
same pattern through out the year
Bellow is the structure of the finencial calender I use.
Financial week starts from monday and ends on sunday.
Here is the structure of the financial calender on the paper.
Calender Financial Week Number
01/jan/07 07/jan/07 1
08/jan/07 14/jan/07 2
15/jan/07 21/jan/07 3
22/jan/07 28/jan/07 4
29/jan/07 04/feb/07 1
05/feb/07 11/feb/07 2
12/feb/07 18/feb/07 3
19/feb/07 25/feb/07 4
26/feb/07 04/mar/07 1
05/mar/07 11/mar/07 2
12/mar/07 18/mar/07 3
19/mar/07 25/mar/07 4
26/mar/07 01/apr/07 5
same pattern applies to other months till the end of the year.
on my time dimension table I have datevalue collumn.
If anyway have the solution of this problem please provide me with one.
Your help will be highly appriaciated.
Guyborn
February 15, 2008 at 9:15 am
Try this on:
with FinWeekCTE as
(select *, datediff(day,dateadd(year,datediff(year,0,dateval),0),dateval) / 7 %13 as weeknum
from dates)
select *, case when weeknum=12 then 5 else weeknum%4+1 end as finweek
from finWeekCTE
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 1:59 pm
Matt.
It is unbelievable that within few hours your gave an incredible answer.it is doing exaclty what I want.Thank you vary much.
Now I have to get myself understand how does it works.
Thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply