Same Period Last Year from Date Parameters

[captnmorgan]

I have a report that allows users to select the date range of the data they are reviewing. I now want to add a column of data that compares this years results to that of the same date range from the previous year.

Is there any way to take the date parameters and also get the same time period from the previous year?

Please be gentle...I am by no means a programmer.

Thanks,

Captain Morgan

 

[synapsevampire]

Check: Report->Edit Record Selection->Record

You probably have something like:

{table.date} = {?dateparameter}

Alter this to:

{table.date} >= cdate(year(minimum({?dateparameter}))-1,month(minimum({?dateparameter})),day(minimum({?dateparameter})))
and
{table.date} <= cdate(year(maximum({?dateparameter})),month(maximum({?dateparameter})),day(maximum({?dateparameter})))

-k

 

[dgillz]

Or, try this:

{table.date} in Dateadd((&quot;yyyy&quot;,-1,minimum({?DateRangeParm})) to Dateadd(&quot;yyyy&quot;,-1,maximum({?DateRangeParm}))

This will give you the sames dates of the year as your date range parameter, only one year ago. Make sure your paramter is a RANGE parameter and a date date type.

If you look in the help files, you will see lots of alternatives arguments to &quot;yyyy&quot;, which should help you use the dateadd() function very effectively.


Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

dgillz: That won't pass the SQL to the database

-k

 

[captnmorgan]

dgillz: snyapsevampire is right. I get an error when checking the formula.

synapsevampire: Is there a way to modify your code so that it queries both the date range from the input parameters plus the same time period from the previous year? As written, your code returns the date range from the parameter plus the entire previous year.

Thanks

Captain Morgan

 

[Naith]

Cap,

The selection criteria should be:

(
{Table.Date}in {?dateparameter} or
{Table.Date} >= CDate(year(minimum({?dateparameter}))-1,month(minimum({?dateparameter})),day(minimum({?dateparameter}))) and
{Table.Date} <= CDate(year(maximum({?dateparameter}))-1,month(maximum({?dateparameter})),day(maximum({?dateparameter})))
)

Naith

 

[Reebo99]

SV,

Which version of CR do you use? I tested the post by dgillz and it passed the SQL fine in version 9.


Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

A bit more info....

In the record selection I used :

{BOB_CASH_MGT_LOOKUP.TRN_DATE} in Dateadd(&quot;yyyy&quot;,-1,minimum({?DateRangParm})) to Dateadd(&quot;yyyy&quot;,-1,maximum({?DateRangParm}))

The SQL it produced was :

SELECT &quot;BOB_CASH_MGT_LOOKUP&quot;.&quot;TRN_DATE&quot;
FROM &quot;CASHMGT&quot;.&quot;BOB_CASH_MGT_LOOKUP&quot; &quot;BOB_CASH_MGT_LOOKUP&quot;
WHERE (&quot;BOB_CASH_MGT_LOOKUP&quot;.&quot;TRN_DATE&quot;>=TO_DATE ('01-07-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;BOB_CASH_MGT_LOOKUP&quot;.&quot;TRN_DATE&quot;<TO_DATE ('16-07-2002 00:00:01', 'DD-MM-YYYY HH24:MI:SS'))

Looks fine to me......

Reebo
Scotland (Sunny with a Smile)

 

[captnmorgan]

Naith, I used your suggestion. It worked great!

Thank you all for the help!!

Captain Morgan

 

[synapsevampire]

Reebo: CR 8.5

Any dateadd statement will kill the pass through.

His second formula might work, I didn't check it.

-k

 

[Reebo99]

SV,

must just be a limitation for 8.5, as you can se above, it passed through fine in 9.

Reebo
Scotland (Sunny with a Smile)