Retrieve dates from Selection Filter/SQL Query

[jklewis]

I need to know if there is a way to retrieve dates from the Selection Filter and/or the SQL Query that is built from the Selection Filter.

IE:

Selection Filter:
tbl.date IN MonthToDate

SQL Query:
Where tbl.date between '7/1/12' and '7/5/12'

I would like to see if there is a way to make it reusable.
Works for various types of date inputs:
tbl.date = '7/1/12'
tbl.date IN WeekToDateFromSun
tbl.date BETWEEN @date_start AND @date_stop

The reason for this is that the data returned is not always the same dates as requested. IE: Request MTD but may not be data on the 1st... etc.. etc..

Any help would be greatly appreciated.

 

[pmax9999]

Hi jklewis

The following formulas would provide the 1st and last date in the MonthToDate range:

//{@FromDate}
Minimum(MonthToDate)

//{@ToDate}
Maximum(MonthToDate)


Cheers
Pete

 

[jklewis]

Thanks..

Any idea on automating though and getting what the dates are from the selection criteria or SQL statement itself.

Any help would be greatly appreciated.

Thank you,

 

[pmax9999]

Obviously I misunderstood the problem.

You will need to better explain what you are trying to achieve.

 

[jklewis]

Let's say I have 4 reports.
In one the selection criteria is:
tbl.date IN MonthToDate
Crystal would translate this into the SQL Query of:
tbl.date between '7/1/12' AND '7/7/12'

Another one with
tbl.date IN WeekToDate
SQL = tbl.date Between '7/1/12' AND '7/7/12'

Another
tbl.date between @date_start AND @date_stop
SQL = tbl.date between '7/6/12' AND '7/7/12'

Last
tbl.date = CDATE(2012,07,07)
SQL = tbl.date = '7/7/12'

Is there a way to write a formula that would give me either an array of all dates or min/max of the dates that are included in the filter or the translated SQL Query?

 

[pmax9999]

I'm not certain I understand what you are trying to do, but on the basis that you want a formula you could put into all 4 reports to pull the start/finish dates from the record selection, you could do something like this, expanded to include reports that use week to date, standard date range parameter or an individual date:

//{Date Selection Date}
If 'MonthToDate' in RecordSelection
Then ToText(Minimum(MonthToDate), 'dd/MM/yyyy') + ' to ' + ToText(Maximum(MonthToDate), 'dd/MM/yyyy')