Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2007-06-07 : 03:58:57
|
I've been using Michaels excellent date function [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519[/url] quite honestly it has saved me loads of time (the e-beers are on me)For my purposes I need to work sometimes with Financial Years and I also need the Dates available to SQL Server 7. So I used Michaels function to create a table and added two fields (possibly more to follow). Please note that this has only been done on SQL Server 2000ALTER TABLE DATEINFOADD -- Needs these to allow NULL so that the Alter Table will work.FINANCIAL_YEAR_START_DATE [datetime] null , -- the start date for the financial yearFINANCIAL_YEAR_START_YEAR [smallint] null , -- the year that the financial years starts in depending on the Financial Year Start Date Given that the table is then populated by Michaels function, to set the FINANCIAL_YEAR_START_DATE I used /* @ARBITRARY_FINANICAL_YEAR_START_DATE is SET to any date that would be the start date of a financial year only the day and month parts are important, so in this case the start of the financial year is 1 April*/DECLARE @ARBITRARY_FINANCIAL_YEAR_START_DATE DATETIMESET @ARBITRARY_FINANCIAL_YEAR_START_DATE = '20060401'UPDATE DATEINFOSET FINANCIAL_YEAR_START_DATE = CASE WHEN DATEDIFF(dd,DATEADD(yy, year([DATE]) - YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE), @ARBITRARY_FINANCIAL_YEAR_START_DATE),[DATE]) < 0 Then DATEADD(YY,YEAR([DATE])- YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE) -1, @ARBITRARY_FINANCIAL_YEAR_START_DATE) Else DATEADD(YY,YEAR([DATE])- YEAR(@ARBITRARY_FINANCIAL_YEAR_START_DATE) , @ARBITRARY_FINANCIAL_YEAR_START_DATE) END With that it is trivial to set FINANCIAL_YEAR_START_YEAR to YEAR(FINANCIAL_YEAR_START_DATE)steve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-07 : 17:10:50
|
| I stayed away from the whole subject of financial periods when I wrote F_TABLE_DATE. I found that there are many different methods for defining fiscal years and periods that tend to be industry specific.Many organizations use some variation on calendar months, usually starting with January, but any month is possible.Some use financial years and periods that are designed to make each financial period a whole number of weeks. This is common in businesses, like retail sales, where the business cycle is highly oriented towards weeks. One way is to have 12 financial periods in a year with 4 weeks, 5 weeks, and 4 weeks in each quarter, with the periods starting on a set day of the week. Another variation is to have 13 financial periods of 4 weeks each; obviously, in this scenario fiscal quarters have no meaning. Because the fiscal years are not exactly the same length as calendar years, it is necessary to have an extra week in the year every few years. Usually the year has a nominal start date, like February 1, with an actual start date of January 29 through February 4, similar to the logic for ISO weeks.There may be others out there. It seems to be limited only by the imagination of accountants and what the tax man will accept.Does anyone know of any other variations?Suggestions for additional columns you might want:FINANCIAL_YEAR_NAME: FY 2007 or FY 2007-2008, etc.FINANCIAL_QUARTER: 1,2,3,etc.FINANCIAL_QUARTER_NAME: FY Q1, FY Quarter 1, etc. FINANCIAL_PERIOD: 1,2,3,etc.FINANCIAL_PERIOD_NAME: Jan, FY Period 01, etc. FINANCIAL_WEEK: 1,2,3,etc.FINANCIAL_WEEK_NAME: FY Week 01, FY Week 02,FINANCIAL_YEAR_QUARTER: 200701, 200702, etc. FINANCIAL_YEAR_QUARTER_NAME: FY 2007 Q1, FY 2007 Q1, etc. FINANCIAL_YEAR_PERIOD: 200701, 200702, etc. FINANCIAL_YEAR_PERIOD_NAME: FY 2007 Jan, FY 2007 Period 01, etc. FINANCIAL_YEAR_WEEK: 200701, 200702, etc. FINANCIAL_YEAR_WEEK_NAME: FY 2007 Week 01, FY 2007 W02, etc. FINANCIAL_YEAR_SEQ_NO: 1,2,3, etc. continuing in sequenceFINANCIAL_QUARTER_SEQ_NO: 1,2,3, etc. continuing in sequenceFINANCIAL_PERIOD_SEQ_NO: 1,2,3, etc. continuing in sequenceFINANCIAL_WEEK_SEQ_NO: 1,2,3, etc. continuing in sequenceThere are enough variations of additional columns that could be needed, that you may want to consider creating another table.CODO ERGO SUM |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-06-08 : 08:14:38
|
| Yes I strongly suspected that you had deliberately left them out. I may need financial quarters based on a quarter of a year starting on the financial year start date. If I do I will post the code if I think it may be useful to some.The thorny one I may need is holidays though I seem to recall there was some code on the site for that - must look it upsteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-08 : 09:30:06
|
quote:
Originally posted by elwoos
Yes I strongly suspected that you had deliberately left them out. I may need financial quarters based on a quarter of a year starting on the financial year start date. If I do I will post the code if I think it may be useful to some.The thorny one I may need is holidays though I seem to recall there was some code on the site for that - must look it upsteve-----------Don't worry head. The computer will do all the thinking from now on.
I think this link has logic for many UK hilodays, and calculations for Easter:Create Date Table with UK & Easter bank holidayshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711Some of the links here may be helpful:Date/Time Info and Script Linkshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762CODO ERGO SUM |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2007-06-11 : 03:30:54
|
| Many thanks MichaelThe first link is the one I had in mind when I made my commentssteve-----------Don't worry head. The computer will do all the thinking from now on. |
|
|
|
|
|
|
|
|
Date extension