Find FIRST Date => Base Date

[tcgoth]

Crystal Reports 8.0, SQL 2000

I am trying to write an effective formula to find the first date (Listing Date) that is the FIRST DATE that is => than the base date (Acq Date) that I am comparing it to.

Being relatively new to Crystal I do not understand if there is a more direct way of getting to the FIRST value rather than having to first find the dates that are => (via subquery?)and then calculate each and every comparison and then use the (MIN) value on the results??

Listing Date field could be any number of values that must be compared.

For example:

Acq Date Listing Date
1/1/2008 10/1/2007
11/12/2007
12/23/2007
1/3/2008
2/23/2008
3/1/2008

Desired Result Output:

"Listing Date" 1/3/2008

I then need to calculate the number of days that have passed which I can do via formula CurrentDate - "Listing Date".

Thanks in advance for any assistance!

 

[dgillz]

Q: Do you want to show all these dates, or just the first one and the difference in days?

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

You could use a record selection formula like this:

{table.listdate} >= {table.acqdate}

Then insert a group field at the level you want to evaluate the acqdate, and then use a formula like this to do your calculation:

Currentdate - minimum({table.listdate},{table.groupfield})

-LB

 

[tcgoth]

I do not need to show any of the dates on the report. The only result that I need to show on the report is the resulting number of days (CurrentDate-Listing Date) AS DOM

Thanks!

 

[tcgoth]

lbass - thanks for the info.

The only thing I don't get is the "Group Field". Are you talking about a Group Selection formula, a Group Name, ??

I guess I'm confused as I need to display the result for every record (along with a number of other calculated fields) and maintain my desired report sort.

Can you give me more specifics on the "Group Field" and how that should be done?

Thanks!

 

[lbass]

Don't you want the acq date and the first date out of a set of dates per some listing or something? Otherwise, there would be one Acq date for the entire report, and one calculation result. What other fields are in the report?

-LB

 

[tcgoth]

Not sure if this helps or hurts, but here is the code so far. I added your criteria selection suggestion to the FROM clause where highlighted in red.

I understand the creation of the Formula field that you mention, just not how to use the Group technique to produce only a single result per record.

So...For example:

Acq Date Listing Date
1/1/2008 10/1/2007
11/12/2007
12/23/2007
1/3/2008
2/23/2008
3/1/2008

The formula would identify 1/3/2008 as being the first Listing Date that occurs after the Acq Date. I would then create a formula with the name (DOM) that is calculated as CurrentDate-(formula result - 1/3/2008) that calculates the number of days. DOM would be the field displayed on the report.

SELECT DISTINCT
    rpt_customer."cust_uid", rpt_customer."cust_fname", rpt_customer."cust_lname", rpt_customer."cust_minitial", rpt_customer."cust_numb", rpt_customer."cust_rec_date", rpt_customer."cust_org_name", rpt_customer."cust_company_name", rpt_customer."cust_poc_name", rpt_customer."cust_status", rpt_customer."cust_street1", rpt_customer."cust_city", rpt_customer."cust_state", rpt_customer."cust_opoc_name",
    rpt_service."serv_POC_name", rpt_service."serv_type_desc", rpt_service."serv_status_desc", rpt_service."serv_destination_org_name", rpt_service."serv_source_type",
    form_0005_buyout."mny_established_value", form_0005_buyout."dt_acquisition_date", form_0005_buyout."chk_guaranteed",
    cust_code."code_value",
    rv_v2form_0007_listing_agent_detail_last."nme_agent_name", rv_v2form_0007_listing_agent_detail_last."nme_agent_company",
    rv_v2form_0007_max_listing_date."list_dt", rv_v2form_0007_max_listing_date."list_price", rv_v2form_0007_max_listing_date."Expiration_Date",
    contact."cont_email"
FROM
    rpt_customer INNER JOIN rpt_service ON rpt_customer."cust_uid" = rpt_service."serv_cust_uid" left outer join rv_v2max_homesale_price_referral on rpt_customer.cust_uid = rv_v2max_homesale_price_referral.cust_uid left outer JOIN form_0005_buyout ON rpt_customer."cust_uid" = form_0005_buyout."cust_uid" left outer JOIN cust_code ON rpt_customer."cust_uid" = cust_code."cust_uid" and code_type='C209' LEFT OUTER JOIN form_0072_offer_calculation ON rpt_customer."cust_uid" = form_0072_offer_calculation."cust_uid" left outer JOIN rv_v2form_0007_max_listing_date ON rpt_service."serv_uid" = rv_v2form_0007_max_listing_date."serv_uid" left outer join rv_v2form_0007_listing_agent_detail_last ON rpt_service.serv_uid = rv_v2form_0007_listing_agent_detail_last.serv_uid left outer join contact on rv_v2form_0007_listing_agent_detail_last.serv_destination_contact_uid = contact.cont_uid left outer join [COLOR=red]rv_v2form_0007_listing_detail on rpt_customer.cust_uid=rv_v2form_0007_listing_detail.cust_uid and rv_v2form_0007_listing_detail.dt_revised_date>=form_0005_buyout."dt_acquisition_date" [/color]
WHERE
    rv_v2max_homesale_price_referral.est_sale_date is null and rv_v2max_homesale_price_referral.act_close_date is null and rpt_service.serv_source_type='Government' and rpt_service.serv_type in ('c212','c218') and form_0005_buyout."chk_guaranteed"='Y' and rpt_customer.cust_status <> 'Cancel' and rpt_service."serv_status_desc" not in('>Cancellation','Cancelled')
ORDER BY
    rpt_customer."cust_org_name" ASC

 

[lbass]

Where exactly are you creating this query, as you couldn't have done it in CR 8.0?

All I meant for you to do was add the >= clause to the where clause, have multiple records returned per report customer (looks like your planned group field), and then use my earlier suggested formula in a group section with the details suppressed.

-LB