processing too long...

[patty1]

I have the below formula in select expert to get last full quarter data automatically , however as the report is downloading, you can see that it is going through every single record (0 of 250000). As a result, when the report gets to the web site, the processing time is not acceptable. Any suggestions?


(if month(CurrentDate) in [1.00 to 3.00] then month({Transaction_History.Post_Date}) in [10.00 to 12.00 ] else
if month(CurrentDate) in [4.00 to 6.00] then month({Transaction_History.Post_Date}) in [1.00 to 3.00 ] else
if month(CurrentDate) in [7.00 to 9.00] then month({Transaction_History.Post_Date}) in [4.00 to 6.00 ] else
if month(CurrentDate) in [10.00 to 12.00] then month({Transaction_History.Post_Date}) in [7.00 to 9.00 ] ) and
if month(CurrentDate) in [1.00 to 3.00]

then Year({Transaction_History.Post_Date}) = Year(CurrentDate)- 1

else
Year({Transaction_History.Post_Date}) = Year(CurrentDate) and

 

[rosemaryl]

Dear Patty,

I know that you are also working on a stored procedure to try to accomplish this.

Because you are on 7.0 and because these "values" can't be passed down to the server the processing time won't be improved.

I think the best suggestion for you is the have your dba add a column to this table for quarter.

DatePart(q,Transaction_History.Post_Date) would give your the quarter (I don't know if this is correct syntax for Sybase, though).

Then in Crystal the formula would be easy and would be passed, something like:

If DatePart(q, currentdate) = 4 then Quarter = 3
and so on...

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[patty1]

thanks ro - I am still trying with the stored procedure - asking my dba to do anything is a last resort for me.

Appreciate all the help though.

 

[rosemaryl]

Dear Patty,

You need to get yourself a reference.

While you can make generalities regarding SQL across all dbs - Syntax requirements are very specific.

Naith had a good suggestion earlier, go to the Sybase forum here, describe your problem, post your query and ask them for help. I am sure that they will be able to answer your question.

I am sorry I couldn't be more helpful.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.