select date based on max/min

[ksaab2002]

Hi,

Could someone please advise how to write a formula to select dates from one date field (DateA) based on that date being within the max/min range of dates in another date field (DateB)?

This needs to be done without input from an end user. Using CR9 and SQL 2000.

Thanks!

 

[synapsevampire]

How can a date field have both a minimum and a maximum in it? Do you mean within a certain grouping, or?

"max/min range of dates in another date field (DateB)?"

Perhaps you just mean something like:

{table.datea} in minimum({table.dateb}) to maximum({table.dateb})

Which would return a true or false.

If you're trying to filter rows or some such, state specifics.

-k

 

[ksaab2002]

I was hoping that simply returning a True or False might work, allow me to explain in more detail, maybe you could advise of a better approach.

I have two tables, Charge and Sale, that I can only link on a client code.

Charge.TrxDate is the date the charge was posted by a credit company. Sale.PstDate was the date the sale was posted (usually within a day +/- of Charge.TrxDate)

What I need to see is all records from Charge and only the records from Sale where the Sale.PstDate is within the Min/Max date range of Charge.TrxDate.

The report I have has no groupings.

Hope that helps clarify.

 

[lbass]

You would need a left join from Charges to Sale. Then you could add your sales table fields and conditionally suppress these fields (format field->common->suppress->x+2) using a formula like:

{sales.pstdate} < minimum({charge.trxdate},{charge.clientcode}) or
{sales.pstdate} > maximum(({charge.trxdate},{charge.clientcode})

-LB

 

[synapsevampire]

Why can you only link on a client code?

They both have dates.

You might use suppression as LB suggests, but you'd also need to add a group by the cliennt code.

-k

 

[ksaab2002]

Hi,

I can only link on client code because the Charge table is fed by a file sent to me over which I have no control.

What I need is a report with the Charge records and any possibly related Sale records from my accounting data..the only thing that lets me know if they are possibly related is the dates which are related but never match up exactly.

I may have to just pull the info from two reports. One from Charge and one from Sale.

Thank you both for your insights,