Record Selection on two date fields

[MiagiSan]

Hi there,

I am using CR10 with an SQL DB. I am currenly working on a report that will show me the number of support cases that were either opened or closed in a date range.

This record selection works fine:

{CASE.BUSINESS_UNIT}="SSPSG" and
{CASE.ATTRIBUTE_ID}="PS_PSGPH" and
{CASE.CLOSED_DTTM} in {?Date}

When I add the Open Field, it takes forever. (you can see Crystal counting thousands of records in the bottom right corner of the screen.)

({CASE.BUSINESS_UNIT}="SSPSG" and
{CASE.ATTRIBUTE_ID}="PS_PSGPH" and
{CASE.CLOSED_DTTM} in {?Date})
or
({CASE.BUSINESS_UNIT}="SSPSG" and
{CASE.ATTRIBUTE_ID}="PS_PSGPH" and
{CASE.OPEN_DTTM} in {?Date})

Is there a way that I can optimize this record selection? I have also tried this:

{CASE.BUSINESS_UNIT}="SSPSG" and
{CASE.ATTRIBUTE_ID}="PS_PSGPH" and
({CASE.OPEN_DTTM} in {?Date} or {CASE.CLOSED_DTTM} in {?Date})

This returned only the cases that were opened in the date range.

Thanks very much..I hope I have included enough infomation.

R

 

[lbass]

I think your last attempt should work with one tweak:

{CASE.BUSINESS_UNIT}="SSPSG" and
{CASE.ATTRIBUTE_ID}="PS_PSGPH" and
(
(
{CASE.OPEN_DTTM} in {?Date} and
(
isnull({CASE.CLOSED_DTTM}) or
{CASE.CLOSED_DTTM} >= CASE.OPEN_DTTM}
)
) OR
{CASE.CLOSED_DTTM} IN {?Date}
)

-LB

 

[MiagiSan]

VERY NICE!

Thank you lbass!!