Financail calendar...seed data

View previous topic View next topic Go down

Financail calendar...seed data

Post  GBS74 on Wed Jul 29, 2009 5:29 pm

hi
I am tring to write pl/sql procedure to create financial calendar seed date. I have confusion about ... how to code to get financial week /period/start and end date for calendar if financial year and quarter start at first monday of April. any idea or script ?
regards

GBS74

Posts : 4
Join date : 2009-07-29

View user profile

Back to top Go down

Re: Financail calendar...seed data

Post  alex.caminals on Mon Sep 28, 2009 8:49 am

I worked in a project in big corporation where they were using a fiscal calendar. We calculated the fiscal dates using the Oracle database function "ADD_MONTHS". In that scenario, the fiscal year began on 1st of March. So we had to substract two months to the calendar date to get the fiscal date.

SELECT any_date calendar_date, ADD_MONTHS (any_date, -2) fiscal_date
FROM DUAL

In your scenario, where the fiscal year begins on the first Monday of April the formula will be a little bit different. What I would do is something like this:

1. Calculate the "day_gap" for each year between your fiscal date and the calendar year (depending on the number of years to store, it may be easier to do that manually than finding a formula).
2. Substract the "day_gap" to each date in order to get the fiscal date (e.g. fiscal date 1-Jan-2009 will be the first Monday of April, 2-Jan-2009 the first Tuesday or April and so on).

That should make it. Let me know if you need further help.

Best regards,
avatar
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 41
Location : Barcelona (Spain)

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum