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 |
|
leahsmart
Posting Yak Master
133 Posts |
 Posted - 2004-08-10 : 06:32:37
|
Hi Reader!I need to do a Financial YTD report. I have a field called MonthAsNumber, this contains a date like '2004/05/01', I need to AVG all the data from the MonthAsNumber to the beginning of the financial year that the MonthAsNumber is in. Do any of you know a good way to find the beginning of the financial year? The report will cover many Financial years.SELECT AVG(Score) as ScoreFROM tblScoresWHERE DateAssessed BETWEEN (SomeKindOfFunction) AND MonthAsNumberGROUP BY MonthAsNumber ThanksLeah |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-08-10 : 07:18:28
|
| Hi Leah,I've just finished on a project doing this kind of thing.My advice to you is to create some kind of Calendar table which has the calendardate as well as the corresponding financialmonth.Your finance department(I assume this would be a report for your finance department) should be able to supply you with this perhaps in a spreadsheet format.What we have also done because a lot of our reports compare this years financial week and month with the equivalent of last years, is we have asked them to create the next few years financial calendars a few years in advance.Why you ask???Because the financial calendar works in units of weeks and months you sit with this problem 52 * 7 = 364 - each calendar year has 365 (sometimes 366) days, which means that every few years you will end up with a 53 week year.We have therefore asked our financial department to clearly define Business rules as to how this needs to be handled.I hope this helps :)Duane. |
 |
|
|
|
|
|
Financial YTD