# 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)
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.