Using a formula (date) to select rows 1

[HardlyWork]

When I use the CR9 Function "in lastfullmonth" to select my rows of data, I get a 13 page report in about 2 minutes (6+ million row search), when I change the Function to a Furmula which also points to last-month (I have verified this), the search turns into a table scan that runs for hours. I need it to work using the formula rather than the function for various reasons. Any ideas? I noticed that the generated sql includes the date check when I use the Function but it is missing when I use the Formula. I have also tried defining/using a Custom Function with the same results.

Any help will be appreciated.

 

[paulmarr]

Could you post your formula please?

Cheers
paulmarr

 

[lbass]

You should be able to use a selection formula something like the following and have it pass to the SQL:

{table.date} >= currentdate - day(currentdate)-day(currentdate - day(currentdate))+1 and
{table.date} < currentdate - day(currentdate)+1

-LB

 

[HardlyWork]

Hi paulmarr, thanks for your reply. My record selection works jst fine when I select using:
{DISBURSEMENTS.ACTUAL_DISB_DATE} in lastfullmonth
but when I try to select based on:
{DISBURSEMENTS.ACTUAL_DISB_DATE} < {SYSTEM_DATES.PREVIOUS_EOM} and
{DISBURSEMENTS.ACTUAL_DISB_DATE} > {SYSTEM_DATES.PREVIOUS_EOQ})
it takes hours to complete. Mind you that these 2 tables are not linked in any way since the SYSTEM.DATES is a stand-alone table while the other tables are all application tables that are logically linked.

Thanks for your help.

 

[lbass]

That's probably the issue. Did you try my suggestion? It passes to the SQL statement, so should be speedy.

-LB

 

[HardlyWork]

Thanks for replying.

I guess a full explanation of the problem is in order. I need to run reports without parameters and yet, it has to extract data for the previous month or previous quarter, etc. My previous example was just to show what didn't work, the actual dates are more fluid in that it could be any month, any quarter, etc. based on what is on the SYSTEM_DATES table. The SYSTEM_DATES table has an EOW_DATE, EOM_DATE, EOQ_DATE, EOY_DATE, all dates point to "previous" EOW, EOM, EOQ, etc. I can't code "currentdate" or "lastfullmonth" since last-full-month could be 09/2005 according to the SYSTEM_DATES table. I think the problem is the tables not being linked, so, any formula or function I develop to use those dates ultimatly result in the sql not being passed to the server. Sorry for the long and winded explanation, but I hope there is some way out of this.

 

[Turkbear]

Hi,
As a test, link the tables using a < and > link betwen the desired date fields..Like:

{DISBURSEMENTS.ACTUAL_DISB_DATE} < {SYSTEM_DATES.PREVIOUS_EOM}
{DISBURSEMENTS.ACTUAL_DISB_DATE} > {SYSTEM_DATES.PREVIOUS_EOQ})

Should do what you need ( at least for that pairing)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[HardlyWork]

Thanks Turkbear, you get a red star for your response. Linking the system_dates table to the data_table with a > & < links did the trick.