Link to home
Start Free TrialLog in
Avatar of trilian
trilianFlag for United States of America

asked on

Need a SQL statement to select Fiscal years based on current quarter in Crystal

Using BO Crystal 2013
I need to report sales data comparing last fiscal year-quarter to this Fiscal year-quarter. Fiscal years is Dec 1 to Nov 30.
I have a few formulas that will select the data but need it entered as a SQL statement so it will get passed to the database and filter the data much earlier.

If I run the report today it will pull sales data from 6/1/14 to 8/31/14 and 6/1/15 to 8/31/15.

Here are two examples that give me this fiscal year-quarter. It is changed for last fiscal year-quarter.
This year start date:
if Month(CurrentDate) in [12] then
Date(Year(CurrentDate)+1,2,28)
else if
Month(CurrentDate) in [1,2] then
Date(Year(CurrentDate),2,28)
else if
Month(CurrentDate) in [3,4,5] then
Date(Year(CurrentDate),5,31)
else if
Month(CurrentDate) in [6,7,8] then
Date(Year(CurrentDate),8,31)
else if
Month(CurrentDate) in [9,10,11] then
Date(Year(CurrentDate),11,30)

to

This year end date

If Month(CurrentDate) in [12] then
Date(Year(CurrentDate),12,1)
else if
Month(CurrentDate) in [1,2] then
Date(Year(CurrentDate)-1,12,1)
else if
Month(CurrentDate) in [3,4,5] then
Date(Year(CurrentDate),3,1)
else if
Month(CurrentDate) in [6,7,8] then
Date(Year(CurrentDate),6,1)
else if
Month(CurrentDate) in [9,10,11] then
Date(Year(CurrentDate),9,1)
ASKER CERTIFIED SOLUTION
Avatar of Ido Millet
Ido Millet
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James0628
James0628

If Ido's suggestion doesn't give you a solution ...

 I think the start and end formulas that you posted are backwards.  I'm also wondering if your ending (?) date in Feb (02/28) will handle leap years.

 If you are using CR for your report, it may be passing your test to the server.  CR will do that if it can.  It will depend on your datasource, how complicated your tests are, etc.  You could go to Database > Show SQL Query and see what it says.

 James