help create a loop? 1

[LMCRYER]

I am in a CR 9 and CE 9 environment.

I have a report that deals with fiscal month / year data.

The report works great, but I need to set it so that the report KNOWS (at runtime) what fiscal year and month the CURRENT date is in. (I need this so that I can tell the system to go and pull 3 fiscal months of data before and 3 after todays date so the graphs are correct).

Anyway, I am having trouble with how to get the fiscal year and month correct for this. Our setup is totally wierd, and our fiscal year does not always end or start on the same date every year. (We follow April to March but...we use a
5-4-4 cycle)

Our database has a "calendar table" in it and our DBA manages that - and I can get to the table, but am unsure how to pull this out....

Here is what the table looks like:

TABLE = FCAL

FIELDS =

cldnr_yr
period_end_dt_01
period_end_dt_02
period_end_dt_03
period_end_dt_04
(there are 12 of these, one for each period)

The end date in each of these is the end date for that period - meaning we don't hold the start date for the period in there, but it can be calculated by (-1)...

I need a loop of some sort that will go in and do :

1. for this calendar year (from currentdate)
2. loop through each of these fields and figure out where the date falls, and then spit out whatever period I am in.

Am I WAY off track here? Am I making this harder than it needs to be?




LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[synapsevampire]

The calendar table should be improved upon to simplify this.

I would place the responsibility upon the dba to qualify their design by demonstrating the SQL used to extract the current period and year.

This flat design demonstrates to me that they shouldn't be involved in design work.

If given the current date, one would have to iterate through this table to derive the current period and year, and the table doesn't even return it, you then need code to state it.

Suggest to the replacement dba you hire that they use a simpler model as is described and code is provided for within my FAQ:

faq767-4532

This describes characteristics for every date, in your case you would simply return period and year for any date by using:

select fiscalmonth, ficalyear from periods where date = currentdate

Note that each date would also have attributes for holidays, business days, weekend, etc.

-k

 

[LMCRYER]

If it were the DBA's fault we had this mess, I would agree with you, but its an Oracle table that is under a commerical application that we purchased and have no control over the structure. In other words, I'd love to fire the idiot that designed the ERP back end...but it's not in my control to do so (darn!)

That being said, we do have a SQL environment for our own use and I'm printing out your info to pass along to the DBA..

Unfortunately, I still have this report to do and it has to be done by Monday COB..not good! Any other brilliant ideas would be appreciated!

Thanks!!



LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[Kebabmeister]

I'm not sure I understand the problem. If you have a defined cycle then you have at least a date you can use as a known value for a formula which calculates start and end dates.

 

[LMCRYER]

Yes, I can figure out the start and end dates for each period in a fiscal year with no issue.

What I need is a loop or something that will take the currentdate (the date the user is running the report) and go and figure out which period it is in - it needs to loop though the possibilities til it hits the right one.

LMC cryerlisa@hotmail.com

select * from Management where proj_mgmt_skills <> NULL

0 records returned

 

[lbass]

You should be able to check year by something like:

if year(currentdate) = {table.calendar_yr} then//etc.

And check the period by using something like:

if (year({FCAL.period_end_dt_04}) = year(currentdate)-1 and
currentdate in {FCAL.period_end_dt_04}+1 to {FCAL.period_end_dt_01}) then "Period 1" else
if currentdate in {FCAL.period_end_dt_01}+1 to {FCAL.period_end_dt_02} then "Period 2" else
if currentdate in {FCAL.period_end_dt_02}+1 to {FCAL.period_end_dt_03} then "Period 3" else
if currentdate in {FCAL.period_end_dt_03}+1 to {FCAL.period_end_dt_04} then "Period 4"

Not sure the first clause in this last bit will work, but maybe this will help get you started...

-LB