Displaying Relative Quarter Dates

[jemsmom]

I'm Using Crystal Reports XI with SQL Server.

My report requires one date parameter the "Survey Date". The goal of the report is to report the number of deficiencies in quarters for the previous 12 full months from the Survey Date. I can't display the correct quarter dates.

I need to display dates like the example below if the user enters a survey date of '2008-03-11':

Delinquency Reporting Period of 03/01/2007 to 2/29/2008

Quarter 1 Quarter 2 quarter 3 quarter 4
3/1/07-5/31/07 6/1/07-8/31/07 9/1/07-11/30/07 12/1/07-2/29/08


Formulas I'm using to display the Delinquency Reporting Period:

Report Start Date: date(DateAdd ("d", 1, DateAdd("yyyy", -1, {@Report End Date})))

Report End Date:
If Month(DateAdd("d", 1, {?Survey Date})) <> Month({?Survey Date}) Then
formula = {?Survey Date}
Else
formula = DateSerial(Year({?Survey Date}), Month({?Survey Date}), 1 - 1)

End If


I've tried this for Quarter 1, but I get the wrong end date, I return 6/1/2007 instead of 5/31/2007.

date(DateAdd ("d", 1, DateAdd("yyyy", -1, {@Report End Date})))
& " - " &
Date(DateAdd ('q',1,DateSerial(Year({@Report End Date}) - 1, Month({@Report End Date}), Day({@Report End Date})) ))

Any help would greatly be appreciated, thank you in advance.

 

[lbass]

I think you can simplify your report start and report end dates to (using Crystal syntax):

dateadd("yyyy", -1, {@Report End Date}+1)//rpt start

If Month(DateAdd("d", 1, {?Survey Date})) <>
Month({?Survey Date}) Then
{?Survey Date} Else
DateSerial(Year({?Survey Date}), Month({?Survey Date}), 1)-1 //report end date

Then use formulas like this for your quarter dates:

dateserial(year({@report start}), month({@report start}),1); //qtr1 start

dateserial(year({@report start}), month({@report start})+3,1)-1 //qtr1 end

dateserial(year({@report start}), month({@report start})+3,1) //qtr2 start

dateserial(year({@report start}), month({@report start})+6,1)-1 //qtr2 end

dateserial(year({@report start}), month({@report start})+6,1) //qtr3 start

dateserial(year({@report start}), month({@report start})+9,1)-1 //qtr3 end

dateserial(year({@report start}), month({@report start})+9,1) //qtr4 start

dateserial(year({@report start}), month({@report start})+12,1)-1 //qtr4 end

-LB

 

[jemsmom]

Thank you so much LB. That worked perfect!