February 20, 2008 at 2:03 pm
Hi!
I'm trying to calculate the financial week per quater from year 2000 to 2010, the week starts from Monday till Sunday each quater consist of a 4weeker, 4weeker and 5weeker.....so every after 5weeker starts another 4weeker, 4weeker and 5weeker and so on,I have tried using the code below but it doesnt seem to be working for me
Please help
Thank you!!
select intPKTimeDim
,datediff(day,dateadd(year,datediff(year,0,DateValue),0),DateValue) / 7 %13
from tblTimeDimension_W
February 20, 2008 at 2:29 pm
Hello
Try creating a table like the following :
Cretate table tenQuarters
( rowId int identity,
Year int null,
Qtr int null,
Weeks int null,
Start datetime null,
End datetime null )
Then populate each years/qtr with the start and end dates for the specific quarter.
You can use the dateadd function to load the end date based upon the start date and the Weeks value.
The reason I recommend this simplistic table method is that it has been my experience that accounting periods differ each year in what ever pattern the current accounting management is interested in . One company issued the start and ending dates of each period each year as part of the budget building processes. This bled over into the General Ledger definitions of accounting periods. This table design also allow for some flexibility when the accounting year changed from say Jan to Dec to March to Feb etc.
I hope this helps.
Regards,
Terry
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply