DateDiff for two different records

[KalebsDad78]

Hi All,

I am running Crystal XI w/ SQL. I have a report I have created that pulls calls where an ambulance responded to a residence two or more times within a week (7 day period).

For these records, I would like to create a "DateDiff" formula that will allow me to restrict calls that show if the 2nd response was greater than 24 hours after the 1st.

As of now, the data may show we responded to a residence on 3/6 and went back on 3/9. That's of no concern. The information I want is we went to a residence on 3/6 and didn't transport and then we went back 4 hours later and transported.

I have looked at numerous "DateDiff" examples and I can't find one that will pull unique records based on the "Customer Name" group I have created.

Can someone please help me create a formula that will work?

Thank you in advance for all of your help.

 

[lbass]

Since you are comparing sequential records, I think you are limited to suppressing unwanted records. Assuming you have a group on {table.cust} and that you have sorted ascending on {table.date}, you can use a suppression formula on the detail section like this:

(
(
onfirstrecord or
{table.cust} <> previous({table.cust})
) and
{table.cust} = next({table.cust}) and
datediff("h",{table.date},next({table.date})) > 24
) or
(
(
onlastrecord or
{table.cust} <> next({table.cust})
) and
{table.cust} = previous({table.cust}) and
datediff("h",previous({table.date}),{table.date}) > 24
) or
(
{table.cust} = previous({table.cust}) and
datediff("h",previous({table.date}),{table.date}) > 24 and
{table.cust} = next({table.cust}) and
datediff("h",{table.date},next({table.date})) > 24
)
or
distinctcount({table.date},{table.cust}) = 1

-LB

 

[KalebsDad78]

That worked perfectly lbass. Your genius amazes me!

One thing else I would like to surpress are the names that, after the formula surpresses non-applicable fields, it leaves the patient name but has no records under that name. Can I surpress fields that are null after the formula zero's them out?

Thanks again for the help.

 

[lbass]

This is complicated. You would have to save the report as a subreport which you would then add to a GH_a section, with your regular group header fields in GH_b. Link the sub to the main report on the customer field. Then in the sub, create a running total that does a count of some recurring field, evaluate using a formula. For the formula wrap not() around the suppression formula. Reset on change of group. Then create a shared variable:

whileprintingrecords;
shared numbervar cntID := {#runningtotal};

Add a reset formula in the group footer of the main report:

whileprintingrecords;
shared numbervar cntID := 0;

Then suppress GH_b using this formula:

whileprintingrecords;
shared numbervar cntID;
cntID = 0 //note no colon

To make the sub disappear, suppress all sections within the sub, and then format the sub->subreport tab->"suppress blank subreport". Then format GH_a to "suppress blank section."

-LB